大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。
当前系列: 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的区别:

  1. IN()的运算对象可以接数值集合,EXISTS()的运算对象只能是SELECT引导的子查询
  2. IN()后面的子查询需要返回一个确定的属性列表,EXISTS()的子查询只关心是否有“行”返回
  3. 对NULL的处理不同,对比:
    WHERE EXISTS(SELECT NULL) 
    WHERE NULL IN(SELECT NULL))

    EXISTS只检查结果集有无返回行,不检查返回行数据是否为NULL。即:哪怕结果集只有一行,且这一行的值为NULL,EXISTS运算结果也为真。

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

相关子查询

子查询中出现了外部SQL语句中出现的列名(等内容)。

需求-1统计所有学生的最好成绩(包括科目)

直接GROUP,无法得到姓名和成绩以外的其他信息。

使用独立子查询,这样的写法,有没有问题?

SELECT * FROM Scores
WHERE Score IN (SELECT MAX(Score) FROM Scores GROUP BY SName);

因为子查询中Max(Score)没有定位到人,所以很有可能:atai的某科成绩刚好等于xj的最好成绩,但这个成绩并不是atai的最好成绩。

正确的写法:

SELECT * FROM Scores os  -- 定义外部表别名
WHERE Score =
(
SELECT MAX(Score) FROM Scores  
WHERE SName = os.SName -- 使用外部表别名
);

从逻辑上讲,整个过程是:

  1. 先取出Score中的SName (外部SELECT),
  2. 然后拿到该SName的最好成绩(内部SELECT和MAX),
  3. 最后使用该最好成绩在外部进行比较过滤(外部WHERE)
  4. ……

但实际上,查看执行计划,根本没有这么麻烦,数据库大概是首先按SName和Score进行分组/排序,然后取每一组/段的最大值:

#体会#:执行计划的作用,SQL是目标导向的……

需求-2:查出每个学生高于(该学生)平均分数的成绩

SELECT * FROM Scores os
WHERE Score <
(
	SELECT AVG(Score) FROM Scores
	WHERE SName = os.SName
);


窗口函数

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

根据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,但查询结果:

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

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

注意,OVER子句中:

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

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

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

  1. ROW_NUMBER():被称之为“行号”。不考虑ORDER BY列值相等的情形,始终是1,2,3,4,5....这样的连续数值
  2. RANK():被称之为“排序”。如果ORDER BY中两行值相等,相等值会占用名次,假设有两个并列第三,GID就会是:1,2,3,3,5
  3. 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. 使用子查询完成:
    1. 得到每个求助悬赏和最高/最低悬赏之间的差距
    2. 找到从未成为邀请人的用户
    3. 删除每个作者悬赏最低的求助
    4. 查找每个作者最近发布的一篇文章
    5. 查出每一篇求助的悬赏都大于5个帮帮币的作者
  2. 使用派生表(如果需要的话),基于求助(Problem)的发布时间(PublishDateTime)查询求助表,以获得:
    1. 一起帮每月各发布了求助多少篇
    2. 每月发布的求助中,悬赏最多的3篇
    3. 每个作者,每月发布的,悬赏最多的3篇
    注意:
    发布时间是单独的一个日期列
  3. 分别使用子查询和派生表,实现分页的功能。利用执行计划,比较其执行效率。
学习笔记
源栈学历
大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。

作业

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

任何问题,都可以直接加 QQ群:273534701

在当前系列 SQL 中继续学习:

多快好省!前端后端,线上线下,名师精讲

  • 先学习,后付费;
  • 不满意,不要钱。
  • 编程培训班,我就选源栈

更多了解 加:

QQ群:273534701

答疑解惑,远程debug……

B站 源栈-小九 的直播间

写代码要保持微笑 (๑•̀ㅂ•́)و✧

公众号:源栈一起帮

二维码