with内联视图 创建分割列表

with语句理解为：临时创建一个在查询期间存在的view，这个视图仅在查询期间存在，查询之后就消失。

with e as (select rownum, deptno, mgr, job from v)
select deptno from (
   select rownum, deptno from e where mgr = 7698
    union
   select rownum, deptno from e where job= 'SALESMAN'
)

上面的语句相当于：

create or replace view e as
select rownum, deptno,mgr, job from v;

select deptno
from (
select rownum, deptno from e where mgr = 7689
union 
select rownum, deptno from e where job = 'SALESMAN'
)
drop view v

例子：

with x as
 (select h.pur_header_id, h.segment1 from pur_headers_all h)
select segment1
  from (select segment1
          from x
         where segment1 = 'PO201908200035'
        union
        select segment1
          from x
         where segment1 = 'PO201908160020');

例子：取得每名员工所在部门人数的同时，还要取得该员工的经理及其经理所在部门的人数。使用内联视图实现如下

select e.ename as e_name,
         (select count(*)
            from emp ed
           where ed.deptno = e.deptno
           group by ed.deptno) as ed_count,
         m.ename as m_name,
         (select count(*)
            from emp md
           where md.deptno = m.deptno
           group by md.deptno) as md_count
    from emp e, emp m
   where e.mgr = m.empno;

 

select e.ename        AS employ_name,
       dc1.dept_count AS emp_dept_count,
       m.ename        AS manager_name,
       dc2.dept_count AS mgr_dept_count
  from emp e,
       (select deptno, count(*) as dept_count from emp group by deptno) dc1,
       emp m,
       (select deptno,count(*) as dept_count from emp group by deptno) dc2
 where e.deptno = dc1.deptno
   and e.mgr = m.empno
   and m.deptno = dc2.deptno;

WITh dept_count AS
 (SELECT deptno, count(*) as dept_count from emp group by deptno)
select e.ename        AS employ_name,
       dc1.dept_count AS emp_dept_count,
       m.ename        AS manager_name,
       dc2.dept_count AS mgr_dept_count
  from emp e, dept_count dc1, emp m, dept_count dc2
 where e.deptno = dc1.deptno
   and e.mgr = m.empno
   and m.deptno = dc2.deptno;

引出递归的思想

递归函数就是直接或间接调用自身的函数，也就是自身调用自己；

递归时常用的编程技术，其基本思想就是“自己调用自己”，一个使用递归技术的方法即是直接或间接的调用自身的方法。递归方法实际上体现了“以此类推”、“用同样的步骤重复”这样的思想。直接递归程序与间接递归中都要实现当前层调用下一层时的参数传递，并取得下一层所返回的结果，并向上一层调用返回当前层的结果。因此，在递归程序的设计中关键是找出调用所需要的参数、返回的结果及递归调用结束的条件。

实现分割列表

with x(deptno,
cnt,
list,
empno,
len) as
 (select deptno,
         count(*) over(partition by deptno),
         cast(ename as varchar(100)),
         empno,
         1
    from emp e
  union all
  select x.deptno, x.cnt, x.list || ',' || e.ename, e.empno, x.len + 1
    from emp e, x
   where e.deptno = x.deptno
     and e.empno > x.empno)
select deptno, list from x where len = cnt;

第二种写法：

select deptno, listagg(e.ename, ',') within group(order by deptno) as names
  from emp e
 group by deptno;