SqlServer：子查询

目录

• 子查询
  • 子查询语法
  • 查询样例
    • 样例一
    • 样例二
    • 样例三
    • 样例四
• EXISTS 嵌套子查询
  • EXISTS 子句
  • 查询样例
    • 样例一
    • 样例二
• 集合运算
  • 集合运算操作符
  • 查询样例
    • 样例一
    • 样例二
    • 样例三
• ALL、ANY 和 SOME
  • ALL、ANY 和 SOME 的功能
  • 查询样例
    • 样例一
    • 样例二
• 参考资料

子查询#

子查询是嵌套的 SELECT 查询，也就是就是在某个 SELECT 结果集中进行检索。子查询能够将比较复杂的查询分解为几个简单的查询，而且子查询可以嵌套。嵌套查询时先执行内部检查再执行外部查询，内部查询的结果将传递给外层语句，并作为外层语句的查询条件来使用。需要指出子查询是多表查询的特殊情况，并不能替代多表查询。

子查询语法#

任何允许使用表达式的地方都可以使用子查询，使用子查询的 SELECT 语句的一般格式如下。

Copy Highlighter-hljs
SELECT 列名列表
FROM 表名1
WHERE 列名1 运算符
(
    SELECT 列名1
    FROM 表名2
    WHERE 列名2 运算符
    (
        SELECT 列名2
        FROM 表名3
        WHERE 条件
    )
)

查询样例#

样例一#

假设 Student 表中有如下一些数据：

查询与“王丽娜”同班的学生学号、姓名，可以在 WHERE 使用子查询查出班级作为过滤条件。

Copy Highlighter-hljs
SELECT SNO, Sname FROM Student
WHERE CLASS = (SELECT CLASS FROM Student WHERE SNAME = '王丽娜')

样例二#

查询和学号为 101 的同学同月出生的所有学生的 Sno、Sname 和列，使用子查询查出月份作为过滤条件。

Copy Highlighter-hljs
SELECT Sno, Sname, Sbirthday FROM Student
WHERE MONTH(Sbirthday) = (SELECT MONTH(Sbirthday) FROM Student WHERE Sno = '101')

样例三#

查询每人的成绩(学号、课程号、成绩)和本课程平均分，本课程平均分可以用和自己连接的子查询查出。

Copy Highlighter-hljs
SELECT Sno, Cno, Degree,
       (SELECT AVG(Degree) FROM Score sc2 WHERE sc1.Cno = sc2.Cno) 科平均
FROM Score sc1

样例四#

假设此时有成绩表 Course，表中具有以下字段和记录。

查询每人的成绩(学号、姓名、课程名、成绩)和本班本科平均分，本班本科平均分可以用子查询实现。

Copy Highlighter-hljs
SELECT SC.Sno, S.Sname, SC.Cno, C.Cname, SC.Degree,
       (
           SELECT AVG(Degree) 
           FROM Score SC2 
	   JOIN Student S2 ON SC2.Sno = S2.Sno
	   JOIN Course C2 ON SC2.Cno = C2.Cno
	   WHERE S.Sname = S2.Sname AND SC.Cno = C2.Cno 
	) 班科平均
FROM Score SC
JOIN Student S ON SC.Sno = S.Sno
JOIN Course C ON SC.Cno = C.Cno

EXISTS 嵌套子查询#

EXISTS 子句#

EXISTS 和 NOT EXISTS 子句通常和相关子查询一起使用，可以限制外层查询，使其结果集符合子查询的条件，并可以判断某个值是否存在于一系列的值中。

查询样例#

样例一#

当分数表中存在与学生表中相同的学号，查询其学号和姓名。

Copy Highlighter-hljs
SELECT Sno, Sname
FROM Student S
WHERE EXISTS (SELECT * FROM Score WHERE Sno = S.Sno)

样例二#

查询没有成绩同学的姓名和学号，使用 NOT EXISTS 子句实现。

Copy Highlighter-hljs
SELECT SNO, SNAME
FROM Student S
WHERE NOT EXISTS (SELECT * FROM Score WHERE Sno = S.Sno)

集合运算#

集合运算操作符#

可以使用如下操作符，对 2 个 SELECT 结果集进行集合运算。

关键字	功能
UNION	对 2 个结果集做并集运算
INTERSECT	对 2 个结果集做交集运算
EXCEPT	对 2 个结果集做差集运算

查询样例#

样例一#

假设此时有教师表 Teacher，表中具有以下字段和记录。

将教师表和学生表汇总为一个表，使用 UNION 做并集运算。

Copy Highlighter-hljs
SELECT Tname, Tsex FROM Teacher
UNION
SELECT Sname, Ssex FROM Student

样例二#

查询学生表中小于 108 且大于 103 的所有学生信息，使用 INTERSECT 做交集运算。

Copy Highlighter-hljs
SELECT * FROM Student WHERE Sno < '108'
INTERSECT
SELECT * FROM Student WHERE Sno > '103'

样例三#

查询学生表中小于 108 但不大于 103 的所有学生信息，使用 EXCEPT 做差集运算。

Copy Highlighter-hljs
SELECT * FROM Student WHERE Sno < '108'
EXCEPT
SELECT * FROM Student WHERE Sno > '103'

ALL、ANY 和 SOME#

ALL、ANY 和 SOME 的功能#

ALL 是所有，表示全部都满足才返回 true。ANY(SOME)是任意一个，表示有任何一个满足就返回 true。"=ANY"与"IN"相同，"<>ALL"与"NOT IN"相同。

查询样例#

样例一#

查询所以成绩都及格的学生的信息（学号，姓名，课程号，课程名，成绩，最低成绩），可以先使用子查询查出某位同学的所有成绩，再结合 ALL 运算符作为过滤条件。

Copy Highlighter-hljs
SELECT SC.Sno, S.Sname, C.Cname, SC.Degree, 
	(SELECT MIN(Degree) FROM Score SC2 WHERE SC.Sno = SC2.Sno) 最低分
FROM Score SC
JOIN Student S ON SC.Sno = S.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE 60 < ALL(
	SELECT Degree FROM Score010 SC3
	WHERE SC3.Sno = SC.Sno
)

样例二#

查询成绩有不及格的学生的信息（学号，姓名，课程号，课程名，成绩, 最低成绩）可以先使用子查询查出某位同学的所有成绩，再结合 ANY 运算符作为过滤条件。

Copy Highlighter-hljs
SELECT SC.Sno, S.Sname, C.Cname, SC.Degree, 
	(SELECT MIN(Degree) FROM Score SC2 WHERE SC.Sno = SC2.Sno) 最低分
FROM Score SC
JOIN Student S ON SC.Sno = S.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE 60 > ANY(
	SELECT Degree FROM Score SC3
	WHERE SC3.Sno = SC.Sno
)

参考资料#

《SqlServer 2014 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社