sql - 选择在同一列中具有两个不同值的行

Question

我想选择所有通过的学生semester 1 和 4 ，我的查询只显示学生的一行，我期待两行。

SELECT Student.mat_stud, fname, lname, dbirth, materials_notes, semester
FROM Student, Notes 
WHERE Notes.mat_stud = Student.mat_stud
    AND fails_status = 1
    AND Notes.mat_div = 1
    AND semester IN(1 , 4)
    AND Notes.level = 1 
    AND school_year = 2015
Group By Notes.mat_stud
Having count(fname) = 2
Order By Notes.mat_stud

我的数据库表如下所示:

Student
-------------------------
mat_stud    fname   lname
-------------------------
142-3698    fname   name    
142-3699    fname   name        
142-3701    fname   name    
142-3700    fname   name    



Notes
---------------------------------------------------------------
mat_stud    materials_notes     semester    level   school_year
---------------------------------------------------------------
142-3698                        1           1       2015
142-3699                        1           1       2015
142-3701                        1           1       2015
142-3698                        4           1       2015
142-3700                        1           1       2015
142-3700                        4           1       2015

预期结果:

mat_stud    fname   lname   materials_notes     semester
142-3698    fname   name                        1
142-3698    fname   name                        4
142-3700    fname   name                        1
142-3700    fname   name                        4

Answer 1

我通过加入所有通过第 1 学期和第 4 学期的学生的结果来提出这个解决方案:

SELECT Notes.mat_stud FROM Notes 
    WHERE semester IN (1, 4) Group By mat_stud Having COUNT(*) = 2

与所有学生。

SELECT t1.mat_stud, fname, lname, materials_notes FROM Student as t1, Notes 
INNER JOIN ( SELECT Notes.mat_stud FROM Notes 
WHERE semester IN (1, 4) Group By mat_stud Having COUNT(*) = 2) as t2 
ON t2.mat_stud = t1.mat_stud  
WHERE fails_status = 1 
AND Notes.mat_div = 1 
AND Notes.level = 1 
AND school_year = 2015 
AND t1.mat_stud = Notes.mat_stud 
Order By Notes.mat_stud