筛选包含这些信息的记录(数据库原理中的 除法)

--学生选课表
CREATE TABLE SC(
    S# CHAR(2),--学生号
    C# CHAR(2)--课程号
)
GO

INSERT INTO SC VALUES('S1', 'C1')
GO
INSERT INTO SC VALUES('S1', 'C2')
GO
INSERT INTO SC VALUES('S2', 'C1')
GO
INSERT INTO SC VALUES('S2', 'C3')
GO
INSERT INTO SC VALUES('S3', 'C1')
GO
INSERT INTO SC VALUES('S3', 'C2')
GO
INSERT INTO SC VALUES('S4', 'C1')
GO
INSERT INTO SC VALUES('S4', 'C2')
GO
INSERT INTO SC VALUES('S4', 'C3')
GO

--查询出 包含了S1同学所选课程的学生号
SELECT DISTINCT S#
FROM   SC
WHERE  S# NOT IN (SELECT t1.S#
                  FROM   (SELECT S#
                          FROM   SC) AS t1,
                         (SELECT C#
                          FROM   SC
                          WHERE  S# = 'S1') AS t2
                  WHERE  NOT EXISTS(SELECT *
                                    FROM   SC AS a
                                    WHERE  a.S# = t1.S#
                                           AND a.C# = t2.C#))
GO

DROP TABLE SC
GO

 

查询结果：S1,S3,S4