使用GROUPBY的oracle sql查询

Question

我有下面的模式，

学生（名字、姓氏、SID）
已注册（学生ID、课程ID）
课程（CID、课程名称、系）

我需要找出哪些学生上的CSC课比IT课多。CSC和IT位位于部门类别中。我有以下查询，其中列出了所有注册课程的学生以及他们注册了多少CSC课程。

select studentid,count(department) as cscclasses
from enrolled
left join course
on courseid = cid
and department = 'CSC'
group by studentid;

不太确定如何将这个数字与那些注册信息技术课程的人进行比较。

Answer 1

在CTE中，计算每个学生的CSC和IT课程计数

   With CTE
   As
  (
         select studentid,
            SUM(case when 
             department ='CSC' 
             then 1 else 0 
             end) as CSCCount ,
            SUM(case when 
             department ='IT' 
             then 1 else 0 
             end) as ITCount ,
         from enrolled
         left join course
         on courseid = cid
        And Sid =studentid
        group by studentid
  )
 Select E.* FROM
JOIN CTE
On CTE.studentid = E.sid
Where CTE.CSCCount >      CTE.ITCount

Answer 2

您可以尝试使用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