4. 高级特性（8.0）

4.1 CTE（Common Table Expressions）

• 公用表表达式，MySQL8.0 之后的版本才可以使用。
• CTE 生成一个命名临时表，并且只在查询期间有效。
• CTE 临时表在一个查询中可以多次引用以及自引用。（子查询每次都要重新定义）
• 其性能和可读性都要优于子查询。

语法：

• RECURSIVE：表示该 CTE 产生的临时表支持自引用
• cte_name：表名，可以在一次查询中被多次引用
• column_list：列表（与查询结果中的列一一对应）
• query：定义公用临时表的查询语句，将该 query 的查询结果包装成一个 cte 表

WITH [RECURSIVE]
cte_name [(column_list)] AS (
  	query
)
[, cte_name [(column_list)] AS (
		query
)]
SELECT * FROM cte_name;

示例1：

WITH cte AS (
	SELECT title, study_cnt, class_id
    FROM imc_course
    WHERE study_cnt > 100
)
SELECT * FROM cte;

示例2：

# 用公用表达式来生成递归序列
WITH RECURSIVE test AS(
	SELECT 1 AS n
    UNION ALL
    SELECT 1+n FROM test WHERE n<10
)
SELECT * FROM test;

4.2 窗口函数

语法：

function_name([exp])
OVER(				#指定窗口的范围
	[PARTITION BY exp [,....]]			#对查询返回的结果集进行分组
	[ORDER BY exp [ASC[DESC]] [,....]]  #排序
)

场景窗口函数：

函数名	说明
聚合函数	聚合函数都可以作为窗口函数使用
ROW_NUMBER()	返回窗口分区内数据的行号
RANK()	类似于 row_number，只是对于相同数据会产生重复的行号，之后的数据行号会产生间隔。
DENSE_RANK()	类似于 RANK()，区别自在于当组内某行数据重复时，虽然行号会重复，但后续的行号不会产生间隔。

示例1：

WITH test(study_name, class_name, score) AS (
	SELECT 'sqlercn','MySQL',95
    UNION ALL
    SELECT 'tom','MySQL',99
    UNION ALL
    SELECT 'Jerry','MySQL',98
    UNION ALL
    SELECT 'Gavin','MySQL',95
    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 rdr
FROM test
ORDER BY class_name, rw;

结果：

示例2：

# 按学生人数对课程进行排名
# 并列出每类课程学习人数排名前3的课程名称、学习人数以及名词
WITH temp AS(
	SELECT b.class_name, 
			a.study_cnt, 
			RANK() OVER (PARTITION BY class_name ORDER BY study_cnt DESC) AS cnt
	FROM imc_course a
	JOIN imc_class b ON a.class_id = b.class_id 
	ORDER BY class_name, study_cnt DESC
)
SELECT * from temp
WHERE cnt <= 3;

结果：

示例3：

# 查询出每门课程学习人数占本类课程总学习人数的百分比
WITH tmp AS(
	SELECT class_name, 
			title, 
			study_cnt, 
			SUM(study_cnt) OVER (PARTITION BY class_name) AS class_total_cnt
	FROM imc_course a
	JOIN imc_class b ON b.class_id = a.class_id
)
SELECT class_name, title, CONCAT(study_cnt/class_total_cnt * 100, '%')
FROM tmp
ORDER BY class_name;