Oracle Database 11g SQL开发指南学习笔记：高级查询(二)
lect 3 as v,2,'03' as vv from dual
union all
select 4 as v,2,'04' as vv from dual
union all
select 5 as v,1,'05' as vv from dual
union all
select 6 as v,5,'06' as vv from dual
union all
select 7 as v,6,'07' as vv from dual
union all
select 8 as v,5,'08' as vv from dual
union all
select 9 as v,8,'09' as vv from dual
union all
select 10 as v,1,'10' as vv from dual
union all
select 11 as v,10,'11' as vv from dual
union all
select 12 as v,10,'12' as vv from dual
union all
select 13 as v,10,'13' as vv from dual
)
select lpad(' ',2 * level - 1) || vv
from t
start with v = 5 --这个查询条件只是过滤整个查询的起点，也就是从哪个节点开始遍历
--如果没有这个过滤条件，那么会尝试从每个值
connect by prior v = parent_v;
--在start with的条件中使用子查询
with t
as
(
select 1 as v,null parent_v,'01' vv from dual
union all
select 2 as v,1,'02' as vv from dual
union all
select 3 as v,2,'03' as vv from dual
union all
select 4 as v,2,'04' as vv from dual
union all
select 5 as v,1,'05' as vv from dual
union all
select 6 as v,5,'06' as vv from dual
union all
select 7 as v,6,'07' as vv from dual
union all
select 8 as v,5,'08' as vv from dual
union all
select 9 as v,8,'09' as vv from dual
union all
select 10 as v,1,'10' as vv from dual
union all
select 11 as v,10,'11' as vv from dual
union all
select 12 as v,10,'12' as vv from dual
union all
select 13 as v,10,'13' as vv from dual
)
/*
LPAD('',2*LEVEL-1)||VV
-----------------------------
05
06
07
08
09
*/
select lpad(' ', 2 * level - 1) || vv
from t
start with v = (select v from t where vv = '05')
connect by prior v = parent_v;
--通过connect by条件删除分支
with t
as
(
select 1 as v,null parent_v,'01' vv from dual
union all
select 2 as v,1,'02' as vv from dual
union all
select 3 as v,2,'03' as vv from dual
union all
select 4 as v,2,'04' as vv from dual
union all
select 5 as v,1,'05' as vv from dual
union all
select 6 as v,5,'06' as vv from dual
union all
select 7 as v,6,'07' as vv from dual
union all
select 8 as v,5,'08' as vv from dual
union all
select 9 as v,8,'09' as vv from dual
union all
select 10 as v,1,'10' as vv from dual
union all
select 11 as v,10,'11' as vv from dual
union all
select 12 as v,10,'12' as vv from dual
union all
select 13 as v,10,'13' as vv from dual
)
/*
LPAD('',2*LEVEL-1)||VV
-------------------------
05
08
09
*/
select lpad(' ', 2 * level - 1) || vv
from t
start with v = 5
connect by prior v = parent_v
and vv != '06' --会删除分支6，也即是删除节点6，及其子节点7
--这个条件在查询中间过程中进行过滤，会直接删除分支，而不是某个节点
--通过where条件，删除节点
with t
as
(
select 1 as v,null parent_v,'01' vv from dual
union all
select 2 as v,1,'02' as vv from dual
union all
select 3 as v,2,'03' as vv from dual
union all
select 4 as v,2,'04' as vv from dual
union all
select 5 as v,1,'05' as vv from dual
union all
select 6 as v,5,'06' as vv from dual
union all
select 7 as v,6,'07' as vv from dual
union all
select 8 as v,5,'08' as vv from dual
union all
select 9 as v,8,'09' as vv from dual
union all
select 10 as v,1,'10' as vv from dual
union all
select 11 as v,10,'11' as vv from dual
union all
select 12 as v,10,'12' as vv from dual
union all
select 13 as v,10,'13' as vv from dual
)
/*
LPAD('',2*LEVEL-1)||VV
---------------------------
05
07
08
09
*/
select lpad(' ', 2 * level - 1) || vv
from t
where vv != '06' --会删除节点6，但其子节点7还是存在的，where只是对查询的结果进行过滤，所以还是会返回节点7
start with v = 5
connect by prior v = parent_v
4、rollup、cube子