暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL公共表表达式（CTE）实战

MySQL公共表表达式（CTE）实战

TalkingBigData 2021-06-24
2050

1. 什么是子查询

在MySQL中如果一个查询的结果被作为另一个查询的条件，被作为查询条件的语句被称为子查询，引用子查询的语句被称为外部查询。如下查询中，首先select max(sal) from emp
语句查询出职工的最高工资X，然后将工资X作为过滤条件查询出持最高工资的员工的详细信息，其中select max(sal) from emp
语句即为子查询
如果子查询语句的查询结果集包含多条记录，外部查询引用子查询时必须使用集合操作符，如in
操作符等。如下查询中，子查询select deptno from emp where sal>2000
返回工资大于2000的职工所在的部门编号，外部查询返回对应的部门名称
如果子查询直接作为外部查询的from子句，这种子查询被称为派生表子查询。派生表子查询必须取别名，否则执行报错

2. 什么是公共表表达式（CTE）

CTE（Common Table Expression，公共表表达式）是一个命名的临时结果集，它存在于单个语句的作用域中，以后可以在该语句中多次被引用。如下查询语句的with cte as (select 1 as one, 2 as two)
即为公共表表达式，它是一个命名的（名字叫cte）结果集，而且在该条语句中它可以被当做临时表来引用（最后的select语句引用了它）。初次接触CTE是不是感觉跟派生表子查询很像？没错！他们确实在某些方面很像，如它们都有名字、它们都在单条语句中生效。当然他们也有很多区别，主要区别有：
  1. 派生表在查询语句中只能引用一次，而CTE可以引用多次
  2. CTE可以引用自身（递归）
  3. CTE可以引用其他CTE
  4. CTE相比排查表可读性更高，CTE在语句开头定义临时结果集而不是像派生表一样嵌入到查询语句中
CTE的临时结果集的列名可以在select查询语句中通过别名指定（即上述查询的as语句），也可以在定义CTE名字时指定。如下所示：

3. 什么是递归公共表表达式（RCTE）

RCTE（Recursive Common Table Expressions，递归公共表表达式）是一种会引用自身的CTE，一般用于生成序列、遍历层次数据结构。下面看一个生成1至10的序列样例。RCTE有如下几个特点：
  1. 必须使用with recursive定义，否则报错ERROR 1146 (42S02): Table '[db_name].[cte_name]' doesn't exist
  2. as后面定义的语句必须用小括号括起来
  3. 小括号中的查询语句包含两个select子句，两个select子句使用union all或者union distinct连接。第一个select的查询结果集作为初始数据集；第二个select是循环迭代部分
如下RCTE示例用于生成1~10之间的序列，recursive
关键字表示这是一个RCTE（普通CTE也可以使用recursive关键字），第一个select 1
语句用于定义初始数据集；第二个select语句定义循环数据集，每次循环时n
的值都比上一次循环时递增1，直到满足n<10时推出循环

3.1. RCTE案例1（生成序列）

本案例首先定义了sales表结构，包含销售日期（occur_date）、销售金额（price），然后往sales表中插入插入7条测试数据。
mysql> create table sales(occur_date date,price double);

mysql> insert into sales values(str_to_date('2017-01-03','%Y-%m-%d'),100);
mysql> insert into sales values(str_to_date('2017-01-03','%Y-%m-%d'),200);
mysql> insert into sales values(str_to_date('2017-01-06','%Y-%m-%d'),50);
mysql> insert into sales values(str_to_date('2017-01-08','%Y-%m-%d'),10);
mysql> insert into sales values(str_to_date('2017-01-08','%Y-%m-%d'),20);
mysql> insert into sales values(str_to_date('2017-01-08','%Y-%m-%d'),150);
mysql> insert into sales values(str_to_date('2017-01-10','%Y-%m-%d'),5);

如果需要按照销售日期汇总销售金额，即统计每天的销售额。稍微懂点SQL语法的开发人员，很容易写出下面的统计语句：按照销售日期分组，对销售金额应用sum聚合函数，并按照销售日期升序排序。SQL语句及执行结果如下图所示：
你感觉已经大功告成，并把上述执行结果反馈给了前端开发，前端开发可能需要使用构造可视化图表。这时候问题来了，测试人员发现横坐标时间不连续，提bug了。如下图所示，我们想要达到右边的图表效果，即，横坐标时间需要连续，当天没有销售记录时，总销售额展示为0
此时，可以借助RCTE在DAO层通过SQL语句实现上述要求，不需要修改应用代码。如下SQL语句所示，RCTE（as后面的括号部分）部分的作用是生成最小日期和最大时期之间的时间序列，即，生成从2017-01-03至2017-01-10之间的时间序列；最后的select部分拿RCTE的中间结果跟sales表左关联（关联键位销售日期）。对应的SQL语句及查询结果集如下图所示：

3.2. RCTE案例2（生成层次查询结果集）

日常开发中经常会遇到层次型/树状数据，比如区域信息包括国家、省份、城市、区县、村镇等；组织架构包括老板、分管领导、部门领导、小组长、组员；家庭成员关系包含曾祖父、祖父、父亲、儿子、孙子等。本案例构造了区域数据集，city包含区域编码id、区域名称name、上级区域编码pid（如果没有上级区域，此字段为NULL）
create table city(id int primary key not null,name varchar(100),pid int null);

insert into city values(1,'shandong',NULL);
insert into city values(11,'jinan',1);
insert into city values(12,'qingdao',1);
insert into city values(13,'zibo',1);
insert into city values(14,'weilang',1);
insert into city values(111,'gaoxinqu',11);
insert into city values(112,'lixiaqu',11);
insert into city values(113,'shizhongqu',11);
insert into city values(2,'hebei',NULL);
insert into city values(21,'shijiazhuang',2);
insert into city values(22,'tangshanshi',2);
insert into city values(23,'qinhuangdao',2);
insert into city values(211,'changanqu',21);
insert into city values(212,'qiaoxiqu',21);
insert into city values(213,'xinhuaqu',21);
如下图所示，RCTE的初始结果集部分首先查询出所有顶级区域（pid为NULL的区域），RCTE的循环部分将RCET自身和city表进行关联，并构造区域上下级结构。对应的SQL语句和查询结果集如下图所示：

mysql
文章转载自TalkingBigData，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论