SQL - 14.子查询

子查询：将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。

 

先建4张表：T_Book（书），T_Category（书的分类），T_Reader（读者），T_ReaderFavorite（读者喜欢的书）

CREATE TABLE T_Reader (FId INT NOT NULL ,FName VARCHAR(50),FYearOfBirth INT,FCity VARCHAR(50),FProvince VARCHAR(50), FYearOfJoin INT);
CREATE TABLE T_Book (FId INT NOT NULL ,FName VARCHAR(50),FYearPublished INT,FCategoryId INT);
CREATE TABLE T_Category (FId INT NOT NULL ,FName VARCHAR(50));
CREATE TABLE T_ReaderFavorite (FCategoryId INT,FReaderId INT);
INSERT INTO T_Category(FId,FName)
VALUES(1,'Story');
INSERT INTO T_Category(FId,FName)
VALUES(2,'History');
INSERT INTO T_Category(FId,FName)
VALUES(3,'Theory');
INSERT INTO T_Category(FId,FName)
VALUES(4,'Technology');
INSERT INTO T_Category(FId,FName)
VALUES(5,'Art');
INSERT INTO T_Category(FId,FName)
VALUES(6,'Philosophy');

INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(1,'Tom',1979,'TangShan','Hebei',2003);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(2,'Sam',1981,'LangFang','Hebei',2001);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(3,'Jerry',1966,'DongGuan','GuangDong',1995);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(4,'Lily',1972,'JiaXing','ZheJiang',2005);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(5,'Marry',1985,'BeiJing','BeiJing',1999);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(6,'Kelly',1977,'ZhuZhou','HuNan',1995);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(7,'Tim',1982,'YongZhou','HuNan',2001);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(8,'King',1979,'JiNan','ShanDong',1997);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(9,'John',1979,'QingDao','ShanDong',2003);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(10,'Lucy',1978,'LuoYang','HeNan',1996);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(11,'July',1983,'ZhuMaDian','HeNan',1999);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(12,'Fige',1981,'JinCheng','ShanXi',2003);

INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(1,'About J2EE',2005,4);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(2,'Learning Hibernate',2003,4);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(3,'Two Cites',1999,1);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(4,'Jane Eyre',2001,1);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(5,'Oliver Twist',2002,1);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(6,'History of China',1982,2);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(7,'History of England',1860,2);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(8,'History of America',1700,2);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(9,'History of The World',2008,2);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(10,'Atom',1930,3);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(11,'RELATIVITY',1945,3);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(12,'Computer',1970,3);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(13,'Astronomy',1971,3);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(14,'How To Singing',1771,5);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(15,'DaoDeJing',2001,6);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(16,'Obedience to Authority',1995,6);

INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,1);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(5,2);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(2,3);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(3,4);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(5,5);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,6);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,7);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(4,8);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(6,9);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(5,10);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(2,11);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(2,12);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,12);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(3,1);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,3);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(4,4);

 

1.单值做为子查询：

最早年份：SELECT MIN(FYearPublished) FROM T_Book

最晚年份：SELECT MAX(FYearPublished) FROM T_Book

 

SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT MAX(FYearPublished)  FROM T_Book) AS f4

 

2.只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。

下面的是错误的：SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book)

 

3.如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。

-- 读者加入的年份2001是，2003年的

SELECT * FROM T_Reader 
WHERE FYearOfJoin IN (2001,2003)

 

出版了书的年份加入了读者

--读者加入那一年出版了书
SELECT * FROM T_Reader 
WHERE FYearOfJoin IN
(
select FYearPublished FROM T_Book
)

 

4.限制结果集。返回第3行到第5行的数据（ ROW_NUMBER 不能用在where子句中，所以将带行号的执行结果作为子查询，就可以将结果当成表一样用了）：

（1）整个表数据

select * from T_TempEmployee

 

 

（2）用 ROW_NUMBER()OVER(PARTITION BY COLUMN ORDER BY COLUMN) 

对FAge

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY FAge DESC) 是先把xlh列降序，再为降序以后的每条FAge记录返回一个序号。

select ROW_NUMBER() over(order by FAge desc) as rownum,
FIdCardNumber,FName,FAge from dbo.T_TempEmployee

 

 

（3）这时候就可以使用子查询了

 

 

 

 

select * from
(
select ROW_NUMBER() over(order by FAge desc) as rownum,
FIdCardNumber,FName,FAge from dbo.T_TempEmployee
) as e1
where e1.rownum >=3 and e1.rownum <=5