我写了一个查询，可以找到特定班级性别学生的人数，但是我找不到一种可以对两种性别都可以做到的查询方法。我尝试两次编写查询（针对每个性别），然后使用UNION将它们组合在一起，但也无法做到这一点。
查询＃1
SELECT MAX(Class.Class_ID) AS Class_ID, COUNT(Enrolled_In.Reg_No) AS Male_Students
FROM Enrolled_In
INNER JOIN Class ON Enrolled_In.Class_ID = Class.Class_ID
INNER JOIN Student ON Enrolled_In.Reg_No = Student.Reg_No
WHERE Class.Class_ID = 'E-100' AND Student.Gender = 'M';
结果：
CLASS_ID MALE_STUDENTS
-------- -------------
E-100 2
查询＃2
SELECT MAX(Class.Class_ID) AS Class_ID, COUNT(Enrolled_In.Reg_No) AS Female_Students
FROM Enrolled_In
INNER JOIN Class ON Enrolled_In.Class_ID = Class.Class_ID
INNER JOIN Student ON Enrolled_In.Reg_No = Student.Reg_No
WHERE Class.Class_ID = 'E-100' AND Student.Gender = 'F';
结果：
CLASS_ID FEMALE_STUDENTS
-------- ---------------
E-100 1
我希望输出像这样，最好使用一个查询：
CLASS_ID MALE_STUDENTS FEMALE_STUDENTS
-------- ------------- ---------------
E-100 2 1
谢谢。
SELECT SUM(case when s.Gender = 'M' then 1 else 0 end) AS Male_Students,
SUM(case when s.Gender = 'F' then 1 else 0 end) AS Female_Students
FROM Enrolled_In e
JOIN Student s ON e.Reg_No = s.Reg_No
WHERE e.Class_ID = 'E-100'
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句