--SNO(學號)
--CNO(科目)
--查詢SNO=001所有的選擇科目相同的所有學生
declare @sc table (SNO varchar(20), CNO varchar(20), 分數 int)
insert @sc select '001', 'english', 80
union all select '001', 'chinese', 70
union all select '002', 'english', 75
union all select '002', 'chinese', 90
union all select '003', 'english', 85
union all select '004', 'chinese', 80
union all select '004', 'english', 80
union all select '004', 'china', 80select a.*
from @sc a
join (select b.sno
from @sc a
join @sc b
on a.cno=b.cno and a.sno='001'
group by b.sno
having count(*)>=(select count(*) from @sc where sno='001')) b
on a.sno=b.sno/*
SNO CNO 分數
-------------------- -------------------- -----------
001 english 80
001 chinese 70
002 english 75
002 chinese 90
004 chinese 80
004 english 80
004 china 80
*/
--CNO(科目)
--查詢SNO=001所有的選擇科目相同的所有學生
declare @sc table (SNO varchar(20), CNO varchar(20), 分數 int)
insert @sc select '001', 'english', 80
union all select '001', 'chinese', 70
union all select '002', 'english', 75
union all select '002', 'chinese', 90
union all select '003', 'english', 85
union all select '004', 'chinese', 80
union all select '004', 'english', 80
union all select '004', 'china', 80select a.*
from @sc a
join (select b.sno
from @sc a
join @sc b
on a.cno=b.cno and a.sno='001'
group by b.sno
having count(*)>=(select count(*) from @sc where sno='001')) b
on a.sno=b.sno/*
SNO CNO 分數
-------------------- -------------------- -----------
001 english 80
001 chinese 70
002 english 75
002 chinese 90
004 chinese 80
004 english 80
004 china 80
*/
FROM Student a
WHERE NOT EXISTS
(
SELECT *
FROM SC b
WHERE S#='1002'
AND NOT EXISTS
(
SELECT * FROM SC
WHERE S#=a.S#
AND C#=b.C#
)
)
select b.S#,b.Sname
from SC a join Student b on a.S#=b.S#
where (select count(1) from SC where S#='1002')
=(select count(1) from (select C# from SC where S#=a.S#) c join (select C# from SC where S#='1002') d on c.C#=d.C#)