赞
踩
备注:测试数据库版本为MySQL 8.0
这个blog我们来聊聊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)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。