if object_id('S') is not null
drop table S
go
create table S(S# nvarchar(50),SN nvarchar(100),SD nvarchar(100),SA int)
insert S select 'V001',N'小明',N'第二班',25
union all select 'V002',N'小日',N'第三班',28
union all select 'V003',N'小月',N'第一班',26
union all select 'V004',N'小萌',N'第四班',21
union all select 'V005',N'小a',N'第a班',21
go
if object_id('C') is not null
drop table C
go
create table C(C# nvarchar(50),CN nvarchar(100))
insert C select 'C001',N'英语课'
union all select 'C002',N'数学课'
union all select 'C003',N'税收基础'
union all select 'C004',N'化学课'
go
if object_id('SC') is not null
drop table SC
go
create table SC(S# nvarchar(50),C# nvarchar(50),G int)
insert SC select 'V001','C002',95
union all select 'V002','C004',45
union all select 'V001','C003',84
union all select 'V003','C002',65
union all select 'V003','C001',75
union all select 'V004','C001',55
union all select 'V004','C002',55
union all select 'V004','C003',55
union all select 'V004','C004',55
go
select * from S
select * from C
select * from SC

--查询选修全部课程的学员姓名和所属单位

\我也想了很久,但就是不知道如何下手,高手能把你做这道的是如何想,简单给我说一说,主要是做题的思路,

双重not exists,论坛有一个类似的,你去找找

OK

楼主参考这个
http://bbs.bc-cn.net/viewthread.php?tid=118141

select * from S where not exists
(
select * from SC where NOT EXISTS
(
select * from C where S#=S.S# AND C#=SC.C#
)
)
不对呀,我照样做,结果不对,而且这种方法有点晕
不太明白
给我正确答案

select SN,SD from S where exists
(
select count(1),S# from C,SC where C.C#=SC.C# and S.S#=S# group by S# having count(1)=(select count(1) from C)
)
双重not exists我也没写出来

我的方法是

Select N.* from S N join
(select a.S#,cnt=count(*) from S a join sc b
on a.S#=b.S#
group by a.S#
having count(*) = (select count(*) from C )
) M

on N.S#=M.S#

select SN,SD from S
where not exists
( select * from C where not exists (select 1 from SC where C.C#=C# and SC.S#=S.S# ) )

原来双重的not exists这种方法是要把含有两个关联字段的放在最内层

还是不太明白...

最精练的果然最难懂的