SELECT：子查询（独立&相关） / 窗口函数 / 派生表·源栈课堂·一起帮

SELECT：子查询（独立&相关） / 窗口函数 / 派生表

当前系列： SQL

子查询

也称“内部查询”或者“嵌套查询”，是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（包括SELECT/UPDATE/DELETE/INSERT）的数据来源或者判断条件的查询。

又可以分为：

独立子查询

子查询部分不依赖于外部变量，选中该部分可以独立运行。

SELECT：查找每个学生的年龄和所有学生平均年龄的差距

SELECT Id, SName, Age, 
Age-(SELECT AVG(Age) FROM Student) AS Gap 
FROM Student;

#试一试#：能不能将上述第二行替换为：

AVG(Age) AS Gap

@想一想@：为什么？

注意语法：子查询部分必须用圆括号（()）括起来。

UPDATE：用id=2的学生年龄替换掉id=1的学生

UPDATE Student 
SET Age = (SELECT Age FROM Student WHERE Id = 2) 
WHERE Id = 1;

DELETE：删除表中的重复记录

思路：首先通过GROUP...MAX()找到唯一值（Id），再进行比对：

DELETE FROM Student
WHERE Id NOT IN(
	SELECT MAX(Id) FROM Student GROUP BY SName);

这里面用到的IN，就是一种集合运算。

mysql需要给子查询额外加一个表别名：

DELETE FROM Student
WHERE Id NOT IN(
select id from(
	SELECT MAX(Id) FROM Student GROUP BY SName) 
t);

除此以外，还有：

ANY或SOME：只要集合中有—个满足条件，结果就为真
```
WHERE Id < ANY (SELECT Id FROM Student)
```
ALL：集合里所有的值都必须满足条件，结果才为真
```
WHERE Id > ALL(SELECT Id FRON Student)
```

但用处不大，一般可以用MAX()和MIN()替换（@想一想@：如何替换？），可读性更高。

EXISTS

检查“结果集”（SELECT Id FROM Student的结果就是一个集合或者结果集）是否有返回数据（或者说“行数据”）：

如果结果集有值，比如SELECT Id FROM Student的结果为：1,2,3，返回真；
否则，返回假。

UPDATE ... WHERE EXISTS (SELECT Id FROM Student WHERE ...);

注意EXISTS和IN的区别：

IN()的运算对象可以接数值集合，EXISTS()的运算对象只能是SELECT引导的子查询
IN()后面的子查询需要返回一个确定的属性列表，EXISTS()的子查询只关心是否有“行”返回
对NULL的处理不同，对比：
```
WHERE EXISTS(SELECT NULL) 
WHERE NULL IN(SELECT NULL))
```
EXISTS只检查结果集有无返回行，不检查返回行数据是否为NULL。即：哪怕结果集只有一行，且这一行的值为NULL，EXISTS运算结果也为真。

结果集为空和结果集值为NULL的区别见下图：

窗口函数

我们来看一个更复杂的需求:

根据Student的Age进行分组，获取每个年龄段的前三位成绩最好的同学。

这里的关键是取前三名，所以分组(GROUP)/子查询啥的都没辙。

必须使用所谓的窗口查询（OVER...PARTITION）功能，其用法如下所示：

SELECT ROW_NUMBER()-- 排名函数
OVER(PARTITION BY Age -- 按Age进行分组(区，一个窗口>
ORDER BY Score) -- 组内按Score排序
AS GID,
Age, SName, score -- 列名
FROM Student;

查询结果如下所示:

可以看到，整个SQL语句，完全没有使用GROUP BY，但查询结果:

首先按Age进行了分组(区)
然后在每个Age组(区)内，又按Score进行了排序
且增添了—列表明组内排名次序的GID(以后取“前三名”时有用)

其中，ROW_NUMBER()被称之为排名函数。其数值就是该行的组内排名，该函数后跟OVER关键字，OVER中使用PARTITION BY指定分组（区）依据，再使用ORDER BY指定组内排序依据。

注意，OVER子句中:

Row_NUMBER()后面必须跟OVER子句
可以省略PARTITION，就会将整个查询结果视为一个分区(想一想:这有什么用呢?)
必须使用ORDER BY。如果没有排序要求，使用:ORDER BY(SELECT 0)，以避开T-SQL语句的要求

查看执行计划（演示），我们发现，其执行过程和我们之前的相关子查询及其类似！

除了ROW_NUMBER()，我们还可以使用其他排名函数：

ROW_NUMBER()：被称之为“行号”。不考虑ORDER BY列值相等的情形，始终是1,2,3,4,5....这样的连续数值
RANK()：被称之为“排序”。如果ORDER BY中两行值相等，相等值会占用名次，假设有两个并列第三，GID就会是：1,2,3,3,5
DENSE_RANK()：被称之为“密集排序”。如果ORDER BY中两行值相等，相等值不会占用名次，如2场景，GID就会是：1,2,3,3,4

派生表

为了获取前三位，我们可能希望这样的SQL语句：

SELECT 
ROW_NUMBER() OVER(PARTITION BY [Age] ORDER BY Score) AS Seq, Id, [Name],Age, Score 
FROM Student 
-- 但以下两种语法都是不被允许的  --WHERE Seq < 3  
--WHERE ROW_NUMBER() OVER(PARTITION BY [UserName] ORDER BY Score) < 3

怎么办呢？可以通过派生（Derived）表来实现：

SELECT * FROM ( 
    SELECT ROW_NUMBER() OVER(PARTITION BY [Age] ORDER BY Score) AS Seq, -- 这是一个计算列，注意一定要加上一个别名 
    Id, [Name], Age, Score FROM Student ) ws -- ws：Windowed Student 这个ns的别名也是一样必须要有的，即使没有后面的WHERE子句  WHERE ws.Seq <= 3 -- 这样就可以使用分区窗口里的Seq了

它本质上就是将子查询结果当做表（这个表就被称之为“派生表”）来使用，直接放到外部查询的FROM子句后面。

注意其语法要求：

派生表必须有表别名。
构建派生表的子查询必须是独立的，不能是相关的

注意虽然在SQL中出现了两次SELECT，但SQL Server并不需要真的的做两次查询。我们可以查看其执行计划：

一次全表扫描完成，为SQL Server的执行计划点赞！

表表达式

派生表属于表表达式（Table Expression），和子查询的区别在于，可以返回多列的、像表一样的结果集。

所以又被称之为“表子查询”，可以理解成返回值为“表”的子查询。

还包括：公共表表表达式（Common Table Expression）、临时表、表变量、视图等，在他们的子查询上，有这么一些共同的要求：

每一列都要求有列名，包括计算列
列名必须唯一
（T-SQL）不保证排序，所以不能/要使用ORDER BY
……

作业

使用子查询完成：
1. 得到每个求助悬赏和最高/最低悬赏之间的差距
2. 找到从未成为邀请人的用户
3. 删除每个作者悬赏最低的求助
4. 查找每个作者最近发布的一篇文章
5. 查出每一篇求助的悬赏都大于5个帮帮币的作者
使用派生表（如果需要的话），基于求助（Problem）的发布时间（PublishDateTime）查询求助表，以获得：
1. 一起帮每月各发布了求助多少篇
2. 每月发布的求助中，悬赏最多的3篇
3. 每个作者，每月发布的，悬赏最多的3篇
注意：
发布时间是单独的一个日期列
分别使用子查询和派生表，实现分页的功能。利用执行计划，比较其执行效率。

学习笔记

源栈学历

键盘敲烂，月薪过万；作业不做，等于没学！

作业

SELECT：子查询 / 派生表：
1. 使用子查询完成：
  1. 得到每个求助悬赏和最高/最低悬赏之间的差距
  2. 找到从未成为邀请人的用户
  3. 删除每个作者悬赏最低的求助
  4. 查找每个作者最近发布的一篇文章
  5. 查出每一篇求助的悬赏都大于5个帮帮币的作者
2. 使用派生表（如果需要的话），基于求助（Problem）的发布时间（PublishDateTime）查询求助表，以获得：
  1. 一起帮每月各发布了求助多少篇
  2. 每月发布的求助中，悬赏最多的3篇
  3. 每个作者，每月发布的，悬赏最多的3篇
  注意：
  发布时间是单独的一个日期列
3. 分别使用子查询和派生表，实现分页的功能。利用执行计划，比较其执行效率。