备注:测试数据库版本为Oracle 11g R2
如需要scott用户下建表及录入数据语句,可参考:
scott建表及录入数据sql脚本
这个blog我们来聊聊Oracle with 语句
对于逻辑复杂的sql,with可以大大减少临时表的数量,提升代码的可读性、可维护性
语句结构:
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(nvl(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(nvl(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(nvl(e1.sal, 0)), 2) avg_sal
from emp e1
group by e1.deptno),
tmp2 as
(select e1.deptno, round(avg(nvl(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;
SQL> --求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
SQL> --主查询的from后面跟了2个临时表,程序可读性不佳
SQL> select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
2 from dept d
3 left join (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal
4 from emp e1
5 group by e1.deptno) tmp1
6 on d.deptno = tmp1.deptno
7 left join (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal
8 from emp e1
9 where e1.sal > 1000
10 group by e1.deptno) tmp2
11 on d.deptno = tmp2.deptno;
DEPTNO AVG_SAL1 AVG_SAL2
------ ---------- ----------
30 1566.67 1690
20 2175 2518.75
10 2916.67 2916.67
40
SQL> --求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
SQL> --2个临时表的定时语句通过with封装成子查询了,程序可读性增强
SQL> with tmp1 as
2 (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal
3 from emp e1
4 group by e1.deptno),
5 tmp2 as
6 (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal
7 from emp e1
8 where e1.sal > 1000
9 group by e1.deptno)
10 select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
11 from dept d
12 left join tmp1
13 on d.deptno = tmp1.deptno
14 left join tmp2
15 on d.deptno = tmp2.deptno;
DEPTNO AVG_SAL1 AVG_SAL2
------ ---------- ----------
30 1566.67 1690
20 2175 2518.75
10 2916.67 2916.67
40
二.with递归
用with递归构造数列
--用with递归构造1-10的数据
with c(n) as
(select 1 from dual union all select n + 1 from c where n < 10)
select n from c;
SQL> --用with递归构造1-10的数据
SQL> with c(n) as
2 (select 1 from dual union all select n + 1 from c where n < 10)
3 select n from c;
N
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected
用with递归构造级联关系
with 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,
lpad('*' ,2*lvl, '*')||ename nm
from emp2
order by lvl,ename
/
SQL> with emp2(ename,empno,mgr,lvl)
2 as
3 (select ename, empno, mgr, 1 lvl from emp where mgr is null
4 union all
5 select emp.ename, emp.empno, emp.mgr, e2.lvl+1
6 from emp, emp2 e2
7 where emp.mgr = e2.empno
8 )
9 select lvl,
10 lpad('*' ,2*lvl, '*')||ename nm
11 from emp2
12 order by lvl,ename
13 /
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 selected