备注:测试数据库版本为MySQL 8.0
今天我们来聊聊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递归构造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)
