使用group by的oracle sql查询

购物车

我有以下架构,

学生(名字,姓氏,SID)
已登记(StudentID,CourseID)
课程(CID,CourseName,部)

我需要弄清楚哪些学生CSC比上更多的ITCSCIT位在找到的Department类别。我有以下查询,其中列出了所有已注册课程的学生以及CSC他们注册了多少门课程。

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

不太确定如何将其与IT课程的入学人数进行比较。

Yohanes Khosiawan许先汉

您可以尝试使用sumcase

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] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章