SQL:如何获得本科和研究生学位的课程？

Question

数据：

Programs  
- degree (name)  
- Course (course list)  

Degrees  
- code (unique identifier)  
- name  
- type (either postgrad or undergrad)  

Course  
- code (unique identifier)   
- name  

因此，undergrad和postgrad学位都有一些课程，我想知道如何获得所有这些课程。

Answer 1

一种方法是聚合：

select p.course
from program p inner join
     degrees d
     on d.Code = p.Degree
where d.type in ('postgrad', 'undergrad')
group by p.course
having count(distinct d.type) = 2;

您只需要在需要名称而不是代码时加入course。

Answer 2

这会帮助你满足你的要求，

SELECT C.Name
from
Program P
inner join Degrees D
on D.Code=P.Degree and (D.Type = 'postgrad' OR D.Type = 'undergrad')
inner join Course C
on C.Code=P.Course
group by C.Name
having count(*)>1

Answer 3

我会用两个子项来做，像这样：

SELECT c.name
from course C 
-- at least one postgrad programs/degrees for this course
inner join (
    select distinct p.course 
    from Program P inner join Degrees D on d_pg.Code=P.Degree 
    where D.Type = 'postgrad'
) as P_pg on C.Code=P_pg.Course
-- at least one undergrad programs/degrees for this course
inner join (
    select distinct p.course 
    from Program P inner join Degrees D on d_pg.Code=P.Degree 
    where D.Type = 'undergrad'
) as P_ug on C.Code=P_ug.Course
where 1=1
;