题目:选择与01同学所选课程相同的同学
1,首先确定01同学的所选课程的数量
select count(DISTINCT sc.CId) from sc where sc.SId ='01'
2,然后建立一个新表tmp,里面存储01同学所选的课程内容
(select distinct sc.CId from sc WHERE SId = '01') as tmp
3,将新表tmp与sc表内连接,条件是两者课程一样(连接确保课程内容的一致性)
select sc.SId from sc
inner join (select distinct sc.CId from sc WHERE SId = '01') as tmp
on tmp.CId = sc.CId
4,在连接的表内将所选课程的数量与01同学所修课程的数量进行比对,相等者留下(确保所选课程数量的一致性)
5,最终整合
select sc.SId from sc
inner join (select distinct sc.CId from sc WHERE SId = '01') as tmp
on tmp.CId = sc.CId
where sc.SId in
( select sc.SId from sc group by sc.SId having count(DISTINCT sc.CId)= (select count(DISTINCT sc.CId) from sc where sc.SId ='01'))
group by sc.SId having count(DISTINCT sc.CId) =
(select count(DISTINCT sc.CId) from sc where sc.SId ='01')