MySQL8.0数据库中with语句讲解

今天我们来聊聊MySQL的with语句对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性。

MySQL 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。

如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本

语句结构:

with subquery_name1 as (subquery_body1),
subquery_name2 as (subquery_body2)
...
select * from subquery_name1 a, subquery_name2 b
where a.col = b.col
...

优势

- 代码模块化
- 代码可读性增强
- 相同查询唯一化

一.提升代码的可读性和可维护性

需求:求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资

-- 求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
-- 主查询的from后面跟了2个临时表，程序可读性不佳
select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
  from dept d
  left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
   from emp e1
  group by e1.deptno) tmp1
on d.deptno = tmp1.deptno
  left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
   from emp e1
  where e1.sal > 1000
  group by e1.deptno) tmp2
on d.deptno = tmp2.deptno;


-- 求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
-- 2个临时表的定时语句通过with封装成子查询了，程序可读性增强
with tmp1 as
 (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
from emp e1
   group by e1.deptno),
tmp2 as
 (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
from emp e1
   where e1.sal > 1000
   group by e1.deptno)
select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
  from dept d
  left join tmp1
on d.deptno = tmp1.deptno
  left join tmp2
on d.deptno = tmp2.deptno;

mysql> -- 求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
mysql> -- 主查询的from后面跟了2个临时表，程序可读性不佳
mysql> select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
->   from dept d
->   left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
->from emp e1
->   group by e1.deptno) tmp1
-> on d.deptno = tmp1.deptno
->   left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
->from emp e1
->   where e1.sal > 1000
->   group by e1.deptno) tmp2
-> on d.deptno = tmp2.deptno;
+--------+----------+----------+
| deptno | avg_sal1 | avg_sal2 |
+--------+----------+----------+
| 10 |  2916.67 |  2916.67 |
| 20 |  2175.00 |  2518.75 |
| 30 |  1566.67 |  1690.00 |
| 40 | NULL | NULL |
+--------+----------+----------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> -- 求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
mysql> -- 2个临时表的定时语句通过with封装成子查询了，程序可读性增强
mysql> with tmp1 as
->  (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
-> from emp e1
->group by e1.deptno),
-> tmp2 as
->  (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
-> from emp e1
->where e1.sal > 1000
->group by e1.deptno)
-> select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
->   from dept d
->   left join tmp1
-> on d.deptno = tmp1.deptno
->   left join tmp2
-> on d.deptno = tmp2.deptno;
+--------+----------+----------+
| deptno | avg_sal1 | avg_sal2 |
+--------+----------+----------+
| 10 |  2916.67 |  2916.67 |
| 20 |  2175.00 |  2518.75 |
| 30 |  1566.67 |  1690.00 |
| 40 | NULL | NULL |
+--------+----------+----------+
4 rows in set (0.00 sec)

mysql>

二.with递归

用with递归构造数列

-- 用with递归构造1-10的数据
with RECURSIVE c(n) as
 (select 1   union all select n + 1 from c where n < 10)
select n from c;

-- 用with递归构造1-10的数据
mysql> with RECURSIVE c(n) as
->  (select 1   union all select n + 1 from c where n < 10)
-> select n from c;
+------+
| n|
+------+
|1 |
|2 |
|3 |
|4 |
|5 |
|6 |
|7 |
|8 |
|9 |
|   10 |
+------+
10 rows in set (0.00 sec)

用with递归构造级联关系

with RECURSIVE emp2(ename,empno,mgr,lvl)
  as
   (select ename, empno, mgr, 1 lvl from emp where mgr is null
union all
select emp.ename, emp.empno, emp.mgr, e2.lvl+1
  from emp, emp2 e2
 where emp.mgr = e2.empno
   )
select lvl,
  concat(repeat('**',lvl),ename) nm
  from emp2
 order by lvl,ename
;

mysql> with RECURSIVE emp2(ename,empno,mgr,lvl)
->   as
->(select ename, empno, mgr, 1 lvl from emp where mgr is null
-> union all
-> select emp.ename, emp.empno, emp.mgr, e2.lvl+1
->   from emp, emp2 e2
->  where emp.mgr = e2.empno
->)
-> select lvl,
->   concat(repeat('**',lvl),ename) nm
->   from emp2
->  order by lvl,ename
-> ;
+------+---------------+
| lvl  | nm|
+------+---------------+
|1 | **KING|
|2 | ****BLAKE |
|2 | ****CLARK |
|2 | ****JONES |
|3 | ******ALLEN   |
|3 | ******FORD|
|3 | ******JAMES   |
|3 | ******MARTIN  |
|3 | ******MILLER  |
|3 | ******SCOTT   |
|3 | ******TURNER  |
|3 | ******WARD|
|4 | ********ADAMS |
|4 | ********SMITH |
+------+---------------+
14 rows in set (0.00 sec)