MySQL 系列教程（笔记）

# SQL 高级特性

# 公用表表达式：CTE（WITH）

官网文档

前面说到过子查询，但是没有仔细说，是因为出现了更好的替代方式，就是 公用表表达式 CTE（Common Table Expressions），

  • MySQL 8.0 之后版本可用
  • CTE 生成一个命名临时表，并且只在查询期间有效
  • 与子查询的区别：CTE 临时表在一个查询中可以多次引用及自引用
WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
SELECT  * FROM cte_name
1
2
3
4
WITH cte (col1, col2) AS   -- 这里给列取名
(		-- 这里组装了一个临时表一共两列
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte; -- 下面用 select 语句从 cte 里面查询数据

->
  col1	col2
  1	2
  3	4
1
2
3
4
5
6
7
8
9
10
11
12

使用课程数据来练习

WITH cte AS(
SELECT  -- 查询学习人数大于 2000 人
	title,
	study_cnt,
	class_id 
FROM
	imc_course 
WHERE
	study_cnt > 2000
)
SELECT * FROM cte
UNION ALL				-- 它可以被多次引用，这里再次使用
SELECT * FROM cte
ORDER BY title -- 为了看着方便，按 title 排序就会出现两条一模一样的数据

->
  title	study_cnt	class_id
  MySQL课程-04045	6646	2
  MySQL课程-04045	6646	2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 用 CTE 递归生成序列

WITH RECURSIVE test as (  -- 使用 RECURSIVE 声明可以递归调用自己
	SELECT 1 AS n	
	UNION ALL
	SELECT 1+n FROM test    -- 这里引用了自身
		WHERE n < 10  -- 当 n 大于 10 就退出递归调用
)
SELECT * FROM test

-> 
  n
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

这个递归，不太好想明白逻辑

-- 递归查询课程评论信息
-- 01. 先查看评论表的数据结构
SELECT * FROM imc_question WHERE replyid > 0

-> 
  quest_id	user_id	course_id	chapter_id	sub_id	replyid	quest_title	quest_content	quest_type	view_cnt	add_time
  2111	3080	5	55	387	788	用户:3080对于 用户:1217关于 PostgreSQL课程-42349第 9章第 2节的评论的	用户:3080对于 用户:1217关于 PostgreSQL课程-42349第 9章第 2节的评论的回复	评论	47	2019-01-28 18:10:44
  2112	25	54	695	5105	873	用户:25对于 用户:2755关于 MySQL课程-21930第 6章第 3节的评论的回复	用户:25对于 用户:2755关于 MySQL课程-21930第 6章第 3节的评论的回复	评论	1	2019-01-28 18:10:44
 
可以看到 quest_id=2111 是对 replyid= 788 的一个回复

-- 要安装树形结构查询出数据

-- 02. 先找到这门课程的所有的问题
SELECT
	* 
FROM
	imc_question 
WHERE
	course_id = 59 
	AND replyid = 0  -- 回复 ID 等于 0 的，说明是一个问题
	
-- 03. 最终语句
WITH RECURSIVE replay ( quest_id, quest_title, user_id, replyid, path ) AS (
	SELECT-- 查询出所有的问答，没有回复的
	quest_id,
	quest_title,
	user_id,
	replyid,
	cast( quest_id AS CHAR ( 200 ) ) AS path 
	FROM
		imc_question 
	WHERE
		course_id = 59 
		AND replyid = 0
  UNION ALL-- 下面开始递归查找他们的评论
	SELECT
		a.quest_id,
		a.quest_title,
		a.user_id,
		a.replyid,
		CONCAT( b.path, ' >> ', a.quest_id ) AS path 
	FROM
		imc_question a -- a 表的数据是 b 表的回复
		JOIN replay b ON a.replyid = b.quest_id 
	) 
SELECT
	* 
FROM
	replay

-> 可以看到：path 字段，表示了一个层级了
quest_id	quest_title	user_id	replyid	path
...
1866	用户:3002关于 MySQL课程-98454第 15章第 1节的评论	3002	0	1866
1888	用户:324关于 MySQL课程-98454第 11章第 6节的问答	324	0	1888
2246	用户:2991对于 用户:324关于 MySQL课程-98454第 4章第 2节的问答的回复	2991	1471	1471 >> 2246
2257	用户:2832对于 用户:2214关于 MySQL课程-98454第 10章第 2节的问答的回复	2832	1379	1379 >> 2257
2320	用户:2476对于 用户:2832对于 用户:2214关于 MySQL课程-98454第 10章第 	2476	2257	1379 >> 2257 >> 2320
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

# 窗口函数：window function

官网文档

一组记录，符合记录的每一条记录都会执行这个函数，不会改变记录的函数。

-- 窗口函数的基本语法
function name([exp])
OVER(    -- 指定了窗口的范围
	[PARTITION BY exp [,...]]  -- 把查询的结果集分成大小不同的分组
  [ORDER BY exp [ASC|DESC] [,...]]  -- 按照哪些字段排序，按照排序后的记录进行编号
)
1
2
3
4
5
6

官网文档对于这一章节的描述花费了好几篇的介绍，挺复杂的，先跟着视频中的思路走下去；

# 排名函数：RANK、DENSE_RANK

  • 聚合函数：比如 avg、count 等函数都可以作为窗口函数使用

  • ROW_NUMBER（）：返回窗口分区内数据的行号

  • RANK()：类似与 row_number ，只是对于相同数据会产生重复的行号，之后的数据行号会产生间隔

    比如：两个数据并列第一，那么下一个数据的行号为 3

  • DENSE_RANK（）：类似于 rank ，区别在于当组内某行数据重复时，虽然行号会重复，但后续的行号不会产生间隔

    比如：两个数据并列第一，那么下一个数据的行号为 2

下面使用一个例子来查看 row_number、rank、dense_rank 的区别

# 范例

-- 使用 cte 定义一张临时表出来
WITH test(study_name,class_name,score) AS (
	SELECT 'sqlercn','MySQL',95
  UNION ALL
  SELECT 'tom','MySQL',99
  UNION ALL
  SELECT 'Jerry','MySQL',99
  UNION ALL
  SELECT 'Gavin','MySQL',98
  UNION ALL
  SELECT 'sqlercn','PostGreSQL',99
  UNION ALL
  SELECT 'tom','PostGreSQL',99
  UNION ALL
  SELECT 'Jerry','PostGreSQL',98
)
SELECT * FROM test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

定义出来的临时表就是学生的学习课程分表数据，执行结果如下

study_name class_name score
sqlercn MySQL 95
tom MySQL 99
Jerry MySQL 99
Gavin MySQL 98
sqlercn PostGreSQL 99
tom PostGreSQL 99
Jerry PostGreSQL 98

下面使用 row_number、rank、dense_rank 查询数据，然后看他们的不同

WITH test(study_name, class_name, score) AS (
    SELECT 'sqlercn', 'MySQL', 95
    UNION ALL
    SELECT 'tom', 'MySQL', 99
    UNION ALL
    SELECT 'Jerry', 'MySQL', 99
    UNION ALL
    SELECT 'Gavin', 'MySQL', 98
    UNION ALL
    SELECT 'sqlercn', 'PostGreSQL', 99
    UNION ALL
    SELECT 'tom', 'PostGreSQL', 99
    UNION ALL
    SELECT 'Jerry', 'PostGreSQL', 98
)
SELECT study_name,
       class_name,
       score,
       row_number() over (
           partition by class_name order by score desc
           ) as rw,
       rank() over (
           partition by class_name order by score desc
           ) as rk,
       dense_rank() over (
           partition by class_name order by score desc
           ) as dr
FROM test
order by class_name, rw
-- 上面三个窗口函数都使用 class_name 来分组，并按照 score 进行降序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

查询结果如下

study_name class_name score rw rk dr
tom MySQL 99 1 1 1
Jerry MySQL 99 2 1 1
Gavin MySQL 98 3 3 2
sqlercn MySQL 95 4 4 3
sqlercn PostGreSQL 99 1 1 1
tom PostGreSQL 99 2 1 1
Jerry PostGreSQL 98 3 3 2

首先上面查询语句的窗口函数功能都是先按照课程名称进行分组，然后在组中按分数进行降序。下面一个一个解说：

  • rw：tom 和 Jerry 的分数都是 99，但是它的行号都是按照顺序排列的
  • rk：tom 和 Jerry 的分数都是 99，他们并列第一，下一个行号中断了，从 3 开始
  • dr：tom 和 Jerry 的分数都是 99，他们并列第一，下一个行号没有中断，从 2 开始

# 业务练习

**范例 1：**按学习人数对课程进行排名，并列出每类课程学习人数排名前 3 的课程名称，显示学习人数以及名次

-- 先查询出按学习人数对课程进行排名的课程
select ic.class_name,
       a.title,
       a.study_cnt,
       rank() over (
           partition by class_name order by study_cnt desc
           ) as rk
from imc_course a
         join imc_class ic on a.class_id = ic.class_id
1
2
3
4
5
6
7
8
9

结果如下，下面是一部分数据

class_name title study_cnt rk
Docker PostgreSQL课程-33460 9588 1
Docker SQLServer课程-90092 6818 2
Docker SQLServer课程-41163 6252 3
Docker SQLServer课程-60895 4585 4
Docker MySQL课程-57732 3562 5
Docker PostgreSQL课程-85484 3104 6
Docker PostgreSQL课程-55484 551 7
Docker PostgreSQL课程-27822 192 8
HadoopDock SQLServer课程-09426 6529 1
HadoopDock PostgreSQL课程-71352 6100 2
HadoopDock PostgreSQL课程-00924 3819 3

可以看到 Docker 分类就有 8 个课程，我们要求是只要每类课程排名前 3 的课程

with tmp as (
    select ic.class_name,
           a.title,
           a.study_cnt,
           rank() over (
               partition by class_name order by study_cnt desc
               ) as rk
    from imc_course a
             join imc_class ic on a.class_id = ic.class_id
  	-- 在这里无法使用 where rk  <= 3，语法错误
)
select *
from tmp
where rk <= 3
-- 需要使用公共表达式搞成一个临时表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

输出结果如下

class_name title study_cnt rk
Docker PostgreSQL课程-33460 9588 1
Docker SQLServer课程-90092 6818 2
Docker SQLServer课程-41163 6252 3
HadoopDock SQLServer课程-09426 6529 1
HadoopDock PostgreSQL课程-71352 6100 2
HadoopDock PostgreSQL课程-00924 3819 3

**范例 2：**每门课程的学习热数占本类课程总学习人数的百分比

with tmp as (
-- 再次提示，这里出来的 class_total 不能直接使用，转成公共表
    select class_name, title, study_cnt, 
    sum(study_cnt) over (partition by class_name) as calss_total
    from imc_course ic
             join imc_class i on ic.class_id = i.class_id)
select *, concat(study_cnt / calss_total * 100, '%') as percentage
from tmp
-- 以上关联 class 表是为了获取它的名称
-- 重点是 sum 与 over 一起使用， over 分组，然后 sum 统计该分组
1
2
3
4
5
6
7
8
9
10

数据如下

class_name title study_cnt calss_total percentage
Docker PostgreSQL课程-85484 3104 34652 8.9576%
Docker PostgreSQL课程-33460 9588 34652 27.6694%
Docker SQLServer课程-41163 6252 34652 18.0422%
Docker PostgreSQL课程-55484 551 34652 1.5901%
Docker SQLServer课程-60895 4585 34652 13.2316%
Docker PostgreSQL课程-27822 192 34652 0.5541%
Docker MySQL课程-57732 3562 34652 10.2793%
Docker SQLServer课程-90092 6818 34652 19.6756%
HadoopDock PostgreSQL课程-00924 3819 21755 17.5546%
HadoopDock SQLServer课程-63911 1498 21755 6.8858%
HadoopDock SQLServer课程-52859 3513 21755 16.1480%

可以看到，每一条数据上都会出现分组统计的总数量，相同分类的数量还都是一致的。再以公共表为基础进行除法运算

← 总结 →