create table student (sid varchar(10),sname varchar(10),sage varchar(10),ssex varchar(10))
create table Sc (sid varchar(10),cid varchar(10),score int)
insert into sc select '0001','001',80
insert into sc select '0001','001',90
insert into sc select '0001','002',70
insert into sc select '0001','003',85
insert into sc select '0002','001',50
insert into sc select '0003','001',60
insert into sc select '0003','002',70
insert into sc select '0004','001',60
insert into sc select '0004','002',70
insert into sc select '0004','003',80
insert into sc select '0004','004',90
insert into student select '0001','swichin','26','男'
insert into student select '0002','lamh','27','男'
insert into student select '0003','ballack','34','男'
insert into student select '0004','lucio','32','男'查询与某学生学的一样或者更多的课程的学生
比如学生0001学习了 001,002,003课程
那么符合条件的学生只有0004这一个了
WHERE NOT EXISTS
(SELECT * FROM SC B WHERE B.sid='0001'
AND NOT EXISTS
(SELECT * FROM SC C WHERE C.cid=B.cid AND C.sid=A.sid))
and sid!='0001'
IF OBJECT_ID('[student]') IS NOT NULL
DROP TABLE [student]
IF OBJECT_ID('[Sc]') IS NOT NULL
DROP TABLE [Sc]
GO
create table student (sid varchar(10),sname varchar(10),sage varchar(10),ssex varchar(10))
create table Sc (sid varchar(10),cid varchar(10),score int)
insert into sc select '0001','001',80
insert into sc select '0001','004',90 --改你一个
insert into sc select '0001','002',70
insert into sc select '0001','003',85
insert into sc select '0002','001',50
insert into sc select '0003','001',60
insert into sc select '0003','002',70
insert into sc select '0004','001',60
insert into sc select '0004','002',70
insert into sc select '0004','003',80
insert into sc select '0004','004',90
insert into student select '0001','swichin','26','男'
insert into student select '0002','lamh','27','男'
insert into student select '0003','ballack','34','男'
insert into student select '0004','lucio','32','男'
--SELECT * FROM student s
--SELECT * FROM sc-->SQL查询如下:
IF OBJECT_ID('[p_test]') IS NOT NULL
DROP PROC [p_test]
GO
CREATE PROC [p_test]
@sid VARCHAR(10)
AS
SELECT distinct b.SID
FROM (
SELECT *
FROM SC
WHERE SID = @sid
) a
LEFT JOIN sc b
ON a.[sid]<>b.sid
AND a.cid = b.cid
WHERE b.sid IS NOT NULL
GROUP BY b.[sid]
HAVING COUNT(1)=(
SELECT COUNT(1)
FROM sc
WHERE SID = @sid
)
GOEXEC p_test '0001'
/*
SID
----------
0004(1 行受影响)
*/