MySQL with语句

MySQL with语句

逻辑复杂的语句，with可以大大减少临时表的数据量，提升代码的可读性和可维护性
MySQL8.0 开始支持with语句
语句结构：
with subquery_name1 as (subquery_body1),
subquery_name2 as (subquery_body2),
…
select *
from subquery_name1 t1,subquery_name2 t2
where t1.col=t2.col

1、提升代码的可读性和可维护性

需求：求每个部门的平均工资，以及剔除薪资低于1000的人之后的平均工资

使用with之前

select d.dname,
       tmp1.avg_sal avg_sal_all,
			 tmp2.avg_sal avg_sal_gt1000
from dept d
left join (
	select deptno,
	       round(avg(ifnull(sal,0)),2) avg_sal
	from emp 
	group by deptno
) tmp1 on tmp1.deptno=d.deptno

left join (
	select deptno,
	       round(avg(ifnull(sal,0)),2) avg_sal
	from emp 
	where sal>1000
	group by deptno
) tmp2 on tmp2.deptno = d.deptno;

使用with之后

with 
avg_sal_all as (
	select deptno,
				 round(avg(ifnull(sal,0)),2) avg_sal
	from emp 
	group by deptno
),
avg_sal_gt1000 as (
	select deptno,
				 round(avg(ifnull(sal,0)),2) avg_sal
	from emp 
	where sal>1000
	group by deptno
)

select d.dname,
			 tmp1.avg_sal avg_sal_all,
			 tmp2.avg_sal avg_sal_gt1000
from dept d
left join avg_sal_all tmp1 on tmp1.deptno=d.deptno
left join avg_sal_gt1000 tmp2 on tmp2.deptno = d.deptno;

2、with递归

with 
recursive c(n) as (
	select 1 from dual
	union all
	select n + 1 from c where n < 10
)

select n from c ;

with recursive emprel(ename,empno,mgr,lvl) as (
	select ename,empno,mgr,1 lvl 
	  from emp 
	 where mgr is null
	union all
	select e1.ename,e1.empno,e1.mgr,e2.lvl+1
	from emp e1
	inner join emprel e2 on e1.mgr=e2.empno
)

select lvl,
			 empno,
			 mgr,
       concat(repeat('**',lvl),ename) nm
from emprel
order by lvl,ename;