>我有一个包含 3 列的表格:course_name, year, semester
.
现在我想找出每年(2001年和2002年(每个学期(S1和S2(都有课程的科目。
我尝试了一个小时写CASE WHEN
和GROUP BY HAVING
但没有得到正确的结果。
table_subjects:
course_name| year| semester
Programming 2001 S1
Programming 2001 S2
Programming 2002 S1
Programming 2002 S2
Law 2001 S1
Law 2001 S2
Law 2002 S2
Science 2001 S1
Science 2001 S2
Management 2002 S2
AI 2001 S1
Database 2001 S1
Database 2001 S2
Database 2002 S1
Database 2002 S2
预期成果:
|course_name|
Programming
Database
您可以在下面尝试 -
演示
select course_name
from t1 a
where year in (2001,2002) and exists (select 1 from t1 b where a.course_name=b.course_name
and a.year=b.year and semester in ('S1','S2') having count(distinct semester)=2)
group by course_name
having count(distinct year)=2
输出:
course_name
Database
Programming
SELECT T.course_name
FROM
(
SELECT course_name
FROM table_subjects
GROUP BY course_name,
year
HAVING COUNT(1) = 2
) T
GROUP BY T.course_name
HAVING COUNT(1) = 2;