我有以下架构，
学生（名字，姓氏，SID）
已登记（StudentID，CourseID）
课程（CID，CourseName，部）
我需要弄清楚哪些学生CSC比上更多的课IT。在CSC与IT位在找到的Department类别。我有以下查询，其中列出了所有已注册课程的学生以及CSC他们注册了多少门课程。
select studentid,count(department) as cscclasses
from enrolled
left join course
on courseid = cid
and department = 'CSC'
group by studentid;
不太确定如何将其与IT课程的入学人数进行比较。
您可以尝试使用sum和case
select studentid
from(
select studentid,
SUM(CASE WHEN department='CSC'
THEN 1
ELSE 0
END) as cscclasses,
SUM(CASE WHEN department='IT'
THEN 1
ELSE 0
END) as itclasses
from enrolled
left join course
on courseid = cid
and department = 'CSC' OR department = 'IT'
group by studentid
)
where cscclasses > itclasses
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句