紧急求助关于用到子查询的元组演算表达式(50分)

[cmg0591]

题目:
设学生选课关系模式为SC（Sno，Cno，Grade），其中Sno为学号，Cno为课程号，Grade为成绩.求跟学号050320197的学生选择相同课程的所有学生的学号

写出元组演算表达式?

[xmlquit]

select sno from tabel where cno in (select cno from tabel where cno = '050320197')

[昵称被占用了]

SQL server的语句是这么写的

select distinct sno from SC a
where not exists (
select 1 from SC b
where sno = a.sNo
and not exists (
select 1 from SC
where cno=b.cNo
and sno = '050320197'
)
)

[hellowork]

declare @t table(Sno varchar(10),Cno int ,Grage int)
insert @t
select '050320197',1,80 union all
select '050320197',2,81 union all
select '050320297',1,82 union all
select '050320297',2,83 union all
select '050320397',2,84 union all
select '050320397',5,85 union all
select '050320497',2,86 union all
select '050320497',3,87

SELECT DISTINCT t.Sno FROM @t as t
WHERE NOT EXISTS(
select 1 from @t as a left join (select * from @t where Sno ='050320197') as b
on a.Cno = b.Cno
where a.Sno = t.Sno AND b.Sno IS NULL)

/*结果
Sno
----------
050320197
050320297
*/

[cmg0591]

第一错了吧,一个人可以选修多门课程啊

[cmg0591]

第二个我测试后发现也不行呀,会把有一个的课程号的就全部输出了

[cmg0591]

第三个用@T结构做可以,但是我换成用题目给定的表的时候的列时不行呀,高手再帮忙看下吧
create table sc
(sno int ,
cno int,
grade int)
insert into sc
values
(1,01,11)
insert into sc
values
(1,02,12)
insert into sc
values
(2,01,11)
insert into sc
values
(2,02,11)
insert into sc
values
(3,01,11)

select distinct t.sno
from sc t
where not exists
(select 1 from sc a left join (select *from sc where sno=1) as b
on a.cno=b.cno
where a.sno=t.sno )
结果没东西显示呀!!!

[hellowork]

抱歉,更改一下,把课程号少的也排除掉:
declare @t table(Sno varchar(10),Cno int ,Grage int)
insert @t
select '050320197',1,80 union all
select '050320197',2,81 union all
select '050320297',1,82 union all
select '050320297',2,83 union all
select '050320397',2,84 union all /*不符合要求*/
select '050320397',5,85 union all /*不符合要求*/
select '050320497',2,86 union all /*不符合要求*/
select '050320597',1,87 /*不符合要求*/

SELECT Sno FROM @t as t
WHERE NOT EXISTS(
select 1 from @t as a left join (select * from @t where Sno ='050320197') as b
on a.Cno = b.Cno
where a.Sno = t.Sno AND b.Sno IS NULL)
GROUP BY Sno
HAVING COUNT(Sno) = (select count(Cno) from @t where Sno ='050320197')


/*结果
Sno
----------
050320197
050320297
*/

[hellowork]

用楼主的表:
if object_id('sc') is not null
drop table sc
GO
create table sc(sno int , cno int, grade int)
insert into sc values(1,01,11)
insert into sc values(1,02,12)
insert into sc values(2,01,11)
insert into sc values(2,02,11)
insert into sc values(3,01,11)

declare @Sno int
set @Sno = 1
SELECT Sno FROM sc as t
WHERE NOT EXISTS(
select 1 from sc as a left join (select * from sc where Sno =@Sno) as b
on a.Cno = b.Cno
where a.Sno = t.Sno AND b.Sno IS NULL)
GROUP BY Sno
HAVING COUNT(Sno) = (select count(Cno) from sc where Sno =@Sno)


/*结果
Sno
----------
1
2
*/

[cmg0591]

SELECT DISTINCT SNO
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.SNO=1 AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.SNO=SCX.SNO AND
SCZ.CNO=SCY.CNO))
这是别人给我答案测试过了,完全正确
谢谢前面的二位高手

[fa_ge]

create table sc(sno int , cno int, grade int)
insert into sc values(1,01,11)
insert into sc values(1,02,12)
insert into sc values(2,01,11)
insert into sc values(2,02,11)
insert into sc values(3,01,11)
insert into sc values(3,03,11)


select distinct Sno from sc a
where exists(select 1 from sc where sno=1 and cno=a.cno)
group by sno
having count(*)=(select count(*)from sc where sno=1)


Sno
-----------
1
2

(2 row(s) affected)