create table student
(
sid int primary key identity(1,1),
sname varchar(20),
sage int,
sex char(2)
)
go
create table course
(
cid int primary key identity(1,1),
cname varchar(20),
tid int
)
go
create table sc
(
id int primary key identity(1,1),
sid int,
cid int,
score int
)
go
create table teacher
(
tid int primary key identity(1,1),
tname varchar(20)
)insert into student values('a',15,'男')
insert into student values('b',12,'女')
insert into student values('c',13,'男')
insert into student values('d',14,'女')
insert into student values('e',15,'男')
insert into student values('f',14,'男')
insert into student values('g',13,'女')
insert into student values('h',12,'男')
insert into student values('i',11,'男')
insert into student values('j',13,'女')insert into teacher values('A')
insert into teacher values('B')
insert into teacher values('C')
insert into teacher values('D')
insert into teacher values('E')insert into course values('语文',1)
insert into course values('数学',2)
insert into course values('英语',3)
insert into course values('物理',4)
insert into course values('化学',5)
insert into sc values(1,1,89)
insert into sc values(1,2,66)
insert into sc values(1,3,33)
insert into sc values(2,1,77)
insert into sc values(2,2,33)
insert into sc values(2,3,77)
insert into sc values(2,4,80)
insert into sc values(3,1,89)
insert into sc values(4,1,65)
insert into sc values(5,1,66)
insert into sc values(5,2,44)
insert into sc values(5,3,55)
insert into sc values(5,4,75)
insert into sc values(5,5,98)
insert into sc values(6,1,66)
insert into sc values(6,2,77)
insert into sc values(6,3,88)
insert into sc values(6,4,99)
insert into sc values(6,5,76)
insert into sc values(7,4,73)
insert into sc values(7,5,52)
insert into sc values(7,3,86)
insert into sc values(8,4,11)
insert into sc values(8,5,22)
insert into sc values(8,3,33)
insert into sc values(8,1,44)
insert into sc values(8,2,55)
insert into sc values(9,5,55)
insert into sc values(10,5,44)
insert into sc values(10,1,22)
insert into sc values(10,2,33)
insert into sc values(10,3,11)
insert into sc values(10,4,55)select * from student
select * from course
select * from scid sid cid score
----------- ----------- ----------- -----------
1 1 1 89
2 1 2 66
3 1 3 33
4 2 1 77
5 2 2 33
6 2 3 77
7 2 4 80
8 3 1 89
9 4 1 65
10 5 1 66
11 5 2 44
12 5 3 55
13 5 4 75
14 5 5 98
15 6 1 66
16 6 2 77
17 6 3 88
18 6 4 99
19 6 5 76
20 7 4 73
21 7 5 52
22 7 3 86
23 8 4 11
24 8 5 22
25 8 3 33
26 8 1 44
27 8 2 55
28 9 5 55
29 10 5 44
30 10 1 22
31 10 2 33
32 10 3 11
33 10 4 55select * from teacher
--12、查询至少学过学号为“1”同学所有课程的其他同学学号和姓名；
select distinct SC.sid,Sname ,cid
from Student,SC
where Student.sid =SC.sid and cid in (select cid from SC where sid=1)sid Sname
----------- --------------------
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
10 j
这是帖子上面的答案 但是题目是1号的所有的课程 应该至少有123 课程学号的学生 所以应该不是上面sql运行的结果
所以请求正确答案
where cid in( select cid from sc where sid=1))
select * from SC 查询出来 有 1 2 3课程 的只有 学号 为 2 5 6 8 10的学生才对 因为他们都有 学
1 2 3的课程 而 按 3楼 朋友的 查询结果是
select sid,sname from student were sid in ( select sid from sc
where cid in( select cid from sc where sid=1))
sid sname
----------- --------------------
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
10 j
FROM sc a
WHERE NOT EXISTS(
SELECT *
FROM sc b
WHERE sid=1
AND NOT EXISTS(
SELECT *
FROM sc c
WHERE c.sid=a.sid AND c.cid=b.cid
))sid
-----------
1
2
5
6
8
10
sid
from
(select
sid,
case when cid in (select distinct cid from SC where sid = 1) then 1 else 0 end kk
from sc ) a
group by a.sid
having sum(kk) = (select count(cid) from SC where sid = 1)
/*
sid
-----------
1
2
5
6
8
10
*/--楼上厉害，我想不出这么复杂的，只能用个容易理解的
where cid in( select cid from sc where sid=1)
GROUP BY SID
HAVING COUNT(1)=(select COUNT(1) from sc where sid=1)
)
from Student,SC
where Student.sid =SC.sid
and cid = all(select cid from SC where sid=1)如何？
基本没这种使用相当于某个值同时等于多个值，基本都为空