现在我要查询至少选修了S1选修的全部课程的学生的学号

[jiang5495]

我有如下的数据表，表名为S，各字段含意分别人，学号，课程号，和分数。
现在我要查询至少选修了S1选修的全部课程的学生的学号，
请问SQL语句怎么写？
SNO CNO SCORE
S1 C1 90
S1 C2 85
S2 C5 57
S2 C6 80
S2 C7 80
S3 C1 80
S3 C2 89
S3 C4 23
S4 C1 34
S4 C2 79
S4 C3 34
S5 C2 89

[bj_kevin51]

顶 学习。。。

[yihunshaol]

create table S(SNO varchar(4),CNO varchar(4), SCORE int)
insert into S select 'S1','C1','90' union all
select 'S1','C1',90 union all
select 'S1','C2',85 union all
select 'S2','C5',57 union all
select 'S2','C6',80 union all
select 'S2','C7',80 union all
select 'S3','C1',80 union all
select 'S3','C2',89 union all
select 'S3','C4',23 union all
select 'S4','C1',34 union all
select 'S4','C2',79 union all
select 'S4','C3',34 union all
select 'S5','C2',89

--查询语句
select distinct sno from s where 
cno in(select distinct cno from s where sno='S1')

结果
S1
S3
S4
S5

[jiang5495]

[Quote=引用 23 楼 yihunshaol 的回复:]
SQL codecreatetable S(SNOvarchar(4),CNOvarchar(4), SCOREint)insertinto Sselect'S1','C1','90'unionallselect'S1','C1',90unionallselect'S1','C2',85unionallselect'S2','C5',57unionallselect'S2','C6',80unionallselect'S2','C7',80unionallselect'S3','C1',80unionallselect'S3','C2',89unionallselect'S3','C4',23unionallselect'S4','C1',34unionallselect'S4','C2',79unionallselect'S4','C3',34unionallselect'S5','C2',89--查询语句selectdistinct snofrom swhere
cnoin(selectdistinct cnofrom swhere sno='S1')

结果
S1
S3
S4
S5

[/Quote]
这个查询结果显然是错的啊

[kasin000]

declare @table table([SNO] varchar(2),[CNO] varchar(2),[SCORE] int)
insert @table
select 'S1','C1',90 union all
select 'S1','C2',85 union all
select 'S2','C5',57 union all
select 'S2','C6',80 union all
select 'S2','C7',80 union all
select 'S3','C1',80 union all
select 'S3','C2',89 union all
select 'S3','C4',23 union all
select 'S4','C1',34 union all
select 'S4','C2',79 union all
select 'S4','C3',34 union all
select 'S5','C2',89


select a.SNO
from @table a,@table b
where b.SNO='S1'
And a.SNO!=b.SNO
And a.CNO=b.CNO
group by a.SNO
having COUNT(*)>1

[kasin000]

SNO
----
S3
S4

(2 行受影响)

[kasin000]

S1应该不算吧。

[dawugui]

create table tb([SNO] varchar(2),[CNO] varchar(2),[SCORE] int)
insert tb
select 'S1','C1',90 union all
select 'S1','C2',85 union all
select 'S2','C5',57 union all
select 'S2','C6',80 union all
select 'S2','C7',80 union all
select 'S3','C1',80 union all
select 'S3','C2',89 union all
select 'S3','C4',23 union all
select 'S4','C1',34 union all
select 'S4','C2',79 union all
select 'S4','C3',34 union all
select 'S5','C2',89
go

select sno from tb where sno <> 'S1' and cno in
(select cno from tb where sno = 'S1')
group by sno having count(1) >= (select count(1) from tb where sno = 'S1')

drop table tb

/*
sno  
---- 
S3
S4

（所影响的行数为 2 行）
*/

[kasin000]

我错了。

[lrjt1980]

条件选择语句。

[icelovey]

-- =============================================
-- Author:      T.O.P
-- Create date: 2009/12/01
-- Version:     SQL SERVER 2005
-- =============================================
declare @tb table([SNO] varchar(2),[CNO] varchar(2),[SCORE] int)
insert @tb
select 'S1','C1',90 union all
select 'S1','C2',85 union all
select 'S2','C5',57 union all
select 'S2','C6',80 union all
select 'S2','C7',80 union all
select 'S3','C1',80 union all
select 'S3','C2',89 union all
select 'S3','C4',23 union all
select 'S4','C1',34 union all
select 'S4','C2',79 union all
select 'S4','C3',34 union all
select 'S5','C2',89

select distinct a.sno 
from @tb A
where sno<>'S1' and not exists(
                    select 1 from @tb b where sno='S1' and 
                        not exists(select 1 from @tb c where a.sno = c.sno and b.cno=c.cno)
                )


--测试结果:
/*
sno  
---- 
S3
S4

（所影响的行数为 2 行）

*/

[-狙击手-]

没看懂

[-狙击手-]

至少选修了S1选修的全部课程的学生的学号

－－

解释一下

[jiang5495]

也就是说S1选修的课程，
目的学生都选修了，
比如说S1选修了C！，C2两门课，
那么S2选了C！，C2，C3是满足条件的查询结果，
不知我有没有解释清楚

[nianran520]

declare @table table([SNO] varchar(2),[CNO] varchar(2),[SCORE] int)
insert @table
select 'S1','C1',90 union all
select 'S1','C2',85 union all
select 'S2','C5',57 union all
select 'S2','C6',80 union all
select 'S2','C7',80 union all
select 'S3','C1',80 union all
select 'S3','C2',89 union all
select 'S3','C4',23 union all
select 'S4','C1',34 union all
select 'S4','C2',79 union all
select 'S4','C3',34 union all
select 'S5','C2',89

select SNO
from
(
select SNO,count(1) as times from @table
where CNO in (select CNO from @table where SNO = 'S1')
group by SNO
) t
where times = (select count(1) from @table where SNO = 'S1')
--结果
--------------
S1
S3
S4

[yanglinqiang]

create table S(SNO varchar(4),CNO varchar(4), SCORE int)
insert into S select 'S1','C1','90' 
union all select 'S1','C2','85'
union all select 'S2','C5','57' 
union all select 'S2','C6','80' 
union all select 'S2','C7','80' 
union all select 'S3','C1','80' 
union all select 'S3','C2','89' 
union all select 'S3','C4','23' 
union all select 'S4','C1','34' 
union all select 'S4','C2','79' 
union all select 'S4','C3','34' 
union all select 'S5','C2','89'
;with tb as
(
select num=count(1),a.SNO 
from S a,S b 
where a.CNO=b.CNO and b.SNO='s1' 
group by a.SNO)
select SNO from tb where num>=(select num from tb where SNO='s1')

--SNO
------
--S1
--S3
--S4
--
--(3 行受影响)

[lang071234]

还是没看懂。还以为等一会会有人答复呢。。在等等。。刚才占沙发好了

[bj_kevin51]

这种面试题，太2了！

[sych888]

select S# from SC where C# in (select C# from SC where S#='s1')
group by S# having count(*)>=(select count(*) from SC where S#='s1');

[jacoblx]

8楼，14楼正解啊，怎么理解呢？书上就是这样的

[流智]

Select sname from S where not exists
(select cno from SC where sno='s1' and cno not in
(select cno from SC where S.sno=sno))