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

2009-12-01 12:32:37 3k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

帕德伯恩大学轴承数据集（PU）介绍 PU数据集介绍一、帕德伯恩大学轴承数据集故障类别说明二、关于损伤描述2.1 损伤组合：2.2 重复和多重损伤的安排:2.3 几何尺寸2.4 损伤程度2.5损伤特征数据集损伤类别三、人为损坏轴承3.1 最常用的人为损伤制造方法是：3.2 本数据集使用的人工损坏是由三种不同方法引起的四、通过加速寿命试验产生真正的轴承损坏样本一、帕德伯恩大学轴承数据集数据集下载地址从帕德伯恩大学获得的PU数据集是一个6203轴承数据集，包括人为诱发的和真实的损害。采用压电加速度计采集轴承座的振动信号，采样频率为64 kHz 阅读详情

我有如下的数据表，表名为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

...全文

【老生谈算法】可视化通道流中瞬时涡量场的算法实现与MATLAB源码分析——瞬时涡量场本文旨在探讨可视化通道流中瞬时涡量场的方法，并通过MATLAB编程实现这一过程。涡量场作为描述流体旋涡强度和方向的重要物理量，对于理解复杂流体运动具有重要意义。本文首先介绍了涡量场的基本概念，随后详细阐述了从测量数据收集到涡量场可视化的完整流程，并给出了相应的MATLAB源码。通过实际运行该源码，我们展示了涡量场的可视化结果，并讨论了其在流动结构识别和动力学行为理解中的应用。关键词：涡量场；流体运动；MATLAB；可视化；PIV一、引言。阅读详情

jiang5495

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

流智 2011-05-08

打赏
举报

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))

jacoblx 2010-04-28

打赏
举报

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

sych888 2009-12-02

打赏
举报

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');

bj_kevin51 2009-12-01

打赏
举报

这种面试题，太2了！

lang071234 2009-12-01

打赏
举报

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

yanglinqiang 2009-12-01

打赏
举报



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 行受影响)

nianran520 2009-12-01

打赏
举报



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

jiang5495 2009-12-01

打赏
举报

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

-狙击手- 2009-12-01

打赏
举报

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

－－

解释一下

-狙击手- 2009-12-01

打赏
举报

没看懂

icelovey 2009-12-01

打赏
举报



-- =============================================

-- 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 行）



*/

lrjt1980 2009-12-01

打赏
举报

条件选择语句。

kasin000 2009-12-01

打赏
举报

我错了。

dawugui 2009-12-01

打赏
举报

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 2009-12-01

打赏
举报

S1应该不算吧。

kasin000 2009-12-01

打赏
举报





SNO

----

S3

S4



(2 行受影响)

kasin000 2009-12-01

打赏
举报



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

jiang5495 2009-12-01

打赏
举报

[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]
这个查询结果显然是错的啊

yihunshaol 2009-12-01

打赏
举报

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

bj_kevin51 2009-12-01

打赏
举报

顶学习。。。

加载更多回复（14）

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

相关推荐