MySql教程

MySQL丨语法丨(四)MySQL8.x 高级特性

本文主要是介绍MySQL丨语法丨(四)MySQL8.x 高级特性,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

同步发布:Hedon丨MySQL丨语法丨(四)MySQL8.x 高级特性 (排版更佳!

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;

结果:

image-20210325185051844

示例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;

结果:

image-20210325190208844

示例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;
image-20210325190552199
这篇关于MySQL丨语法丨(四)MySQL8.x 高级特性的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!