分层查询

1.表示父子关系

  select a.ename || ' works for ' || b.ename as emps_and_mgrs from emp a,emp b
where a.mgr = b.empno

2.表示子-父-祖关系

 select ltrim(sys_connect_by_path(ename,'--->'),'--->') leaf_branch_root

 from emp where level = 3 start with ename = 'MILLER'
connect by prior mgr = empno

 

3.创建表的分层视图

  select ltrim(sys_connect_by_path(ename,'--->'),'--->') emp_tree from emp
start with mgr is null
connect by prior empno = mgr
order by 1

 

4.为给定父行找到所有字行

  select ename from emp start with ename = 'JONES'
 connect by prior empno = mgr

 

5.确认哪些行是叶节点，分支节点和根节点(?)

  select ename,connect_by_isleaf is_leaf,
(select count(*) from emp e where e.mgr = emp.empno
and e.mgr is not null
and rownum = 1) is_branch,
decode(ename,connect_by_root(ename),1,0) is_root
from emp
start with mgr is null
connect by prior empno = mgr
order by 4 desc,3 desc

 

6.

WITH a AS(
SELECT '2010-1-1' rq,'XXX' yz,'YYY' mbz FROM dual
UNION ALL
SELECT '2010-5-1' ,'YYY' ,'ZZZ' FROM dual
UNION ALL
SELECT '2010-1-1' ,'AAA' ,'BBB' FROM dual
)
SELECT CONNECT_BY_ROOT(yz),mbz,yz FROM a
WHERE CONNECT_by_isleaf=1 ---判断是否是叶子节点
START with NOT EXISTS(select 1 from a b where b.mbz=a.yz) ---判断？
CONNECT BY PRIOR mbz=yz

 

7.

有两个表，一个是类型表，简称a ,一个是数据表,简称b，
a表的表结构如下：
id name pid
1 产品 0
2 饮料 1
3 食品 1
4 可乐 2
5 矿泉水 2
6 橙汁 2
7 牛肉 3
8 饼干 3
9 挂面 3

b表的数据如下：
id num(销售量)
4 10
4 5
5 2
6 7
7 10

现在想通过sql得到这种结果
id name num
1 产品 34
  2 饮料 24
  4 可乐 15
  5 矿泉水 2
  6 橙汁 7
  3 食品 10
  7 牛肉 10
即，分类别进行逐级汇总，该怎么写呢？

with a as(
select 1 id,'产品' name,0 pid from dual
union all
select 2 id,'饮料' name,1 pid from dual
union all
select 3 id,'食品' name,1 pid from dual
union all
select 4 id,'可乐' name,2 pid from dual
union all
select 5 id,'矿泉水' name,2 pid from dual
union all
select 6 id,'橙汁' name,2 pid from dual
union all
select 7 id,'牛肉' name,3 pid from dual
union all
select 8 id,'饼干' name,3 pid from dual
union all
select 9 id,'挂面' name,3 pid from dual
)
,b as(
select 4 id,10 num from dual
union all
select 4 id,5 num from dual
union all
select 5 id,2 num from dual
union all
select 6 id,7 num from dual
union all
select 7 id,10 num from dual
)
,c as (
select level lv from dual connect by level < 6
)
select id,name,sumbytype from(
select id,name,kk,sum(tol) over(partition by kk order by kk) sumbytype,row_number() over(partition by kk order by id) rn from(
select distinct id,name,tol,tier,substr(tier,0,lv) kk from(
select id,name,pid,nvl(tol,0) tol,replace(ltrim(sys_connect_by_path(id,'-->'),'-->'),'-->',null) tier from (
select a.id,name,pid,sum(num) tol from a,b where a.id = b.id(+) group by a.id,name,pid order by a.id
) t start with pid=0 connect by prior t.id=t.pid
),c
) order by id
) where rn = 1 and sumbytype != 0
--group by substr(tier,0,lv)

 

1 1 产品 34
2 2 饮料 24
3 3 食品 10
4 4 可乐 15
5 5 矿泉水 2
6 6 橙汁 7
7 7 牛肉 10

 

8.

with temp as(
select '01' code,'江苏省' name,'0' superitemid,1 levelno from dual
union all
select '0101' code,'常州市' name,'01' superitemid,2 levelno from dual
union all
select '0102' code,'南京市' name,'01' superitemid,2 levelno from dual
union all
select '010101' code,'武进区' name,'0101' superitemid,3 levelno from dual
union all
select '02' code,'山东省' name,'0' superitemid,1 levelno from dual
union all
select '0201' code,'济南市' name,'02' superitemid,2 levelno from dual
union all
select '0202' code,'青岛市' name,'02' superitemid,2 levelno from dual
union all
select '0203' code,'淄博市' name,'02' superitemid,2 levelno from dual
union all
select '020101' code,'历城区' name,'0201' superitemid,3 levelno from dual
)
SELECT code itemid,connect_by_root(code) code1,
    connect_by_root(NAME) name1,
    Decode(levelno,2,code,3,superitemid,NULL) code2,
    Decode(levelno,1,NULL,3,(SELECT NAME FROM temp WHERE code=a.superitemid),name) name2,
    Decode(levelno,3,code,NULL) code3,
   Decode(levelno,3,NAME,NULL) name3
    FROM temp a
    START WITH superitemid='0'
    CONNECT BY PRIOR code = superitemid