MySQLWITH语句详解及其应用实例总结

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL的with语句

对于逻辑复杂的sql&＃xff0c;with可以大大减少临时表的数量&＃xff0c;提升代码的可读性、可维护性

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

如需要scott用户下建表及录入数据语句&＃xff0c;可参考:

scott建表及录入数据sql脚本

语句结构:

with subquery_name1 as (subquery_body1),

subquery_name2 as (subquery_body2)

...

select * from subquery_name1 a, subquery_name2 b

where a.col &＃61; b.col

...

优势

– 代码模块化

– 代码可读性增强

– 相同查询唯一化

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

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

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

-- 主查询的from后面跟了2个临时表&＃xff0c;程序可读性不佳

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 &＃61; 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 &＃61; tmp2.deptno;

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

-- 2个临时表的定时语句通过with封装成子查询了&＃xff0c;程序可读性增强

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 &＃61; tmp1.deptno

left join tmp2

on d.deptno &＃61; tmp2.deptno;

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

mysql> -- 主查询的from后面跟了2个临时表&＃xff0c;程序可读性不佳

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 &＃61; 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 &＃61; tmp2.deptno;

&＃43;--------&＃43;----------&＃43;----------&＃43;

| deptno | avg_sal1 | avg_sal2 |

&＃43;--------&＃43;----------&＃43;----------&＃43;

| 10 | 2916.67 | 2916.67 |

| 20 | 2175.00 | 2518.75 |

| 30 | 1566.67 | 1690.00 |

| 40 | NULL | NULL |

&＃43;--------&＃43;----------&＃43;----------&＃43;

4 rows in set (0.00 sec)

mysql>

mysql>

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

mysql> -- 2个临时表的定时语句通过with封装成子查询了&＃xff0c;程序可读性增强

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 &＃61; tmp1.deptno

-> left join tmp2

-> on d.deptno &＃61; tmp2.deptno;

&＃43;--------&＃43;----------&＃43;----------&＃43;

| deptno | avg_sal1 | avg_sal2 |

&＃43;--------&＃43;----------&＃43;----------&＃43;

| 10 | 2916.67 | 2916.67 |

| 20 | 2175.00 | 2518.75 |

| 30 | 1566.67 | 1690.00 |

| 40 | NULL | NULL |

&＃43;--------&＃43;----------&＃43;----------&＃43;

4 rows in set (0.00 sec)

mysql>

二.with递归

用with递归构造数列

-- 用with递归构造1-10的数据

with RECURSIVE c(n) as

(select 1 union all select n &＃43; 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 &＃43; 1 from c where n <10)

-> select n from c;

&＃43;------&＃43;

| n |

&＃43;------&＃43;

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

&＃43;------&＃43;

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&＃43;1

from emp, emp2 e2

where emp.mgr &＃61; e2.empno

)

select lvl,

concat(repeat(&＃39;**&＃39;,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&＃43;1

-> from emp, emp2 e2

-> where emp.mgr &＃61; e2.empno

-> )

-> select lvl,

-> concat(repeat(&＃39;**&＃39;,lvl),ename) nm

-> from emp2

-> order by lvl,ename

-> ;

&＃43;------&＃43;---------------&＃43;

| lvl | nm |

&＃43;------&＃43;---------------&＃43;

| 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 |

&＃43;------&＃43;---------------&＃43;

14 rows in set (0.00 sec)