数据库内核月报

数据库内核月报 － 2018 / 11

MySQL · 最佳实践 · 8.0 CTE和窗口函数的用法

Author: 勋臣

概述

MySql 8新增了CTE(公共表达式)和窗口函数功能，这两个极大的简化了我们复杂查询的写法特别是一些分析类查询的写法。这两个函数极大的提高了我们复杂SQL可读性，使我们的代码后期更易于维护。熟悉数据库的同学，对Count(),Sum(),Max/Min等基本统计函数并不陌生。普通统计函数在每个分组内只能返回一条记录。而新增加的窗口函数和CTE函数可以一个分组返回多条函数。下面我们通过具体的例子来演示这两种函数的具体用法

窗口函数

例1：

. 有一张表结构如下

 mysql> desc mytest;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | YES  |     | NULL    |       |
| name   | text          | YES  |     | NULL    |       |
| course | text          | YES  |     | NULL    |       |
| score  | decimal(10,0) | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

. 它有以下数据：

mysql> select * from mytest;
+------+------+----------+-------+
| id   | name | course   | score |
+------+------+----------+-------+
|    1 | TOM  | Math     |   100 |
|    2 | TOM  | Letter   |    90 |
|    3 | TOM  | English  |    80 |
|    4 | TOM  | Physical |   100 |
|    5 | TOM  | Chemical |    99 |
|    6 | CICI | Math     |    90 |
|    7 | CICI | Letter   |   100 |
|    8 | CICI | English  |    80 |
|    9 | CICI | Physical |    90 |
|   10 | CICI | Chemical |    70 |
|   11 | CoCo | Math     |    90 |
|   12 | CoCo | Letter   |    92 |
|   13 | CoCo | English  |    93 |
|   14 | CoCo | Physical |    94 |
|   15 | CoCo | Chemical |    95 |
+------+------+----------+-------+
15 rows in set (0.00 sec)

问题1：要查询出每一科最高分学生的学号，姓名，成绩和科目。在MySQL8之前主要是通过下面的方式来实现。

答案1：MySql 8之前的方法


mysql>select  x.id,x.name,x.course,x.score 
from ( select t.*,if(@_course=t.course ,@rn:=@rn+1,@rn:=1) as rn ,@_course:=t.course as _course  
	 from
		(select t.* from mytest t order by course,score  desc  ) t , 
		(select @rn:=0 rn ,@_course:='') b 
	 )x  
WHERE rn=1 ORDER BY course;

+------+------+----------+-------+
| id   | name | course   | score |
+------+------+----------+-------+
|    5 | TOM  | Chemical |    99 |
|   13 | CoCo | English  |    93 |
|    7 | CICI | Letter   |   100 |
|    1 | TOM  | Math     |   100 |
|    4 | TOM  | Physical |   100 |
+------+------+----------+-------+
5 rows in set, 5 warnings (0.00 sec)

这个sql虽然高效，但是可读性不强。接下来我们来看看窗口函数怎么实现

答案2：使用窗口函数ROW_NUMBER

mysql> select id,name,course,score 
from (
	select 
		row_number()over(partition by course order by score desc) as rn, 
		id,
		name,
		course,
		score 
	from mytest 
)t where rn=1;

+------+------+----------+-------+
| id   | name | course   | score |
+------+------+----------+-------+
|    5 | TOM  | Chemical |    99 |
|   13 | CoCo | English  |    93 |
|    7 | CICI | Letter   |   100 |
|    1 | TOM  | Math     |   100 |
|    4 | TOM  | Physical |   100 |
+------+------+----------+-------+

答案3 使用CTE方式

mysql> with cte as(
select row_number()over(partition by course order by score desc) as rn, id,name,course,score from mytest
)
select id,name,course,score  from  cte where rn = 1;
+------+------+----------+-------+
| id   | name | course   | score |
+------+------+----------+-------+
|    5 | TOM  | Chemical |    99 |
|   13 | CoCo | English  |    93 |
|    7 | CICI | Letter   |   100 |
|    1 | TOM  | Math     |   100 |
|    4 | TOM  | Physical |   100 |
+------+------+----------+-------+
5 rows in set (0.00 sec)

通过对比上面的三种答案，发现CTE和窗口函数极大简化了我们的sql语句。我们的sql更容易读懂。

结论：

本文通过一个简单的例子介绍了MySQL新增的CTE和窗口函数来简化我们SQL开发的难度。关于具体的CTE和窗口函数的具体语法请查阅Mysql的官方文档。