MySQL 8.0终于开始支持with语句了,对于复杂查询,可以不用写那么多的临时表了。
with
可以大大减少临时表的数量,提升代码的可读性、可维护性
一提升代码的可读性和可维护性
需求:求每个部门的平均工资,以及剔除薪资低于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; +--------+----------+----------+ | 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)
/** * 求每个部门的平均工资,以及剔除薪资低于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; +--------+----------+----------+ | 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)
二 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; +------+ | 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 ; +------+---------------+ | 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)