如何组合两个具有不同where条件的sql查询？

bmvo0sr5 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(459)

我写了一个查询，可以找到某个班级中某一性别的学生人数，但是我找不到一个方法来写一个查询，可以对两个性别都做同样的事情。我尝试了两次（针对每个性别）编写查询，然后使用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

谢谢您。

sql oracle oracle11g

来源：https://stackoverflow.com/questions/62227802/how-to-combine-two-sql-queries-with-different-where-conditions

2条答案

按热度按时间

eqqqjvef1#

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'

赞(0）回复(0）举报 2021-07-26

snz8szmq2#

请尝试以下操作

select
  c.class_id, 
  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 ei
join class c
on ei.class_id = c.class_id
join student s
on ei.reg_no = s.reg_no
where c.class_id = 'e-100'
group by
  c.class_id

赞(0）回复(0）举报 2021-07-26

我来回答

如何组合两个具有不同where条件的sql查询？

2条答案

相关问题

热门标签

最新问答