请教一个复杂的带树形和分析函数的sql

Question

有两个表，一个是类型表，简称a ,一个是数据表,简称b，
a表的表结构如下：
id  name   pid
1   产品    0
2   饮料    1
3   食品    1
4   可乐    2
5   矿泉水  2
6   橙汁    2
7   牛肉    3
8   饼干    3
9   挂面    3b表的数据如下：
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
即，分类别进行逐级汇总，该怎么写呢？

Answer 1

 
SQL> select * from a;
 
 ID NAME                 PID
--- -------------------- ---
  1 产品                   0
  2 饮料                   1
  3 食品                   1
  4 可乐                   2
  5 矿泉水                 2
  6 橙汁                   2
  7 牛肉                   3
  8 饼干                   3
  9 挂面                   3
 
9 rows selected
 
SQL> select * from b;
 
 ID NUM
--- ---
  4  10
  4   5
  5   2
  6   7
  7  10
 
SQL> 
SQL> with t1 as (select id,name,pid,sys_connect_by_path(id,'/')||'/' path from a start with pid=0 connect by prior a.id=a.pid),
  2       t2 as(select id,sum(num) num from b group by id)
  3  select a.id,a.name,sum(b.num) from a,t2 b ,t1
  4   where instr(t1.path,'/'||a.id||'/')>0 and instr(t1.path,'/'||a.id||'/')<=instr(t1.path,'/'||b.id||'/')
  5   group by a.id,a.name;
 
 ID NAME                 SUM(B.NUM)
--- -------------------- ----------
  1 产品                         34
  2 饮料                         24
  3 食品                         10
  4 可乐                         15
  5 矿泉水                        2
  6 橙汁                          7
  7 牛肉                         10
 
7 rows selected
 
SQL> 

Answer 2

厉害，没有看懂。另外，能把层次关系给带出来吗，即希望按树形的方式来展现他们
ID NAME                 SUM(B.NUM)     level
--- -------------------- ----------
  1 产品                         34         1
  2 饮料                         24         2
    4 可乐                         15       3
  5 矿泉水                        2         3
  6 橙汁                          7         3
3 食品                         10           2  7 牛肉                         10         3

Answer 3

谢谢了先，这边让我们公司的DBA在搞呢，说是没问题，不过现在还没弄出来

Answer 4

with t1 as (select id,sum(num) num from b group by id),
     t2 as (select  a.id,a.name,b.num,sys_connect_by_path(a.id,'/')||'/' path,rownum rn from a left  join t1 b on b.id=a.id  start with pid=0 connect by  prior a.id=a.pid order siblings by id)   
 select c.id,c.name,nvl(sum(nvl(d.num,0)),0) from t2 c,t2 d
  where d.path like c.path||'%'
  group by c.id,c.name,c.rn
  order by c.rn asc

Answer 5

SQL> with t1 as (select id,sum(num) num from b group by id),
  2    t2 as (select a.id,a.name,nvl(b.num,0) num,sys_connect_by_path(a.id,'/')||'/' path,rownum rn from a left join t1 b on b.id=a.id start with pid=0 connect by prior a.id=a.pid order siblings by id)
  3   select c.id,c.name,sum(d.num) from t2 c,t2 d
  4    where d.path like c.path||'%'
  5    group by c.id,c.name,c.rn
  6    having sum(d.num)>0
  7    order by c.rn asc
  8  ;
 
 ID NAME                 SUM(D.NUM)
--- -------------------- ----------
  1 产品                         34
  2 饮料                         24
  4 可乐                         15
  5 矿泉水                        2
  6 橙汁                          7
  3 食品                         10
  7 牛肉                         10
 
7 rows selected
 
SQL> 
SQL> with t1 as (select id,sum(num) num from b group by id),
  2    t2 as (select a.id,a.name,nvl(b.num,0) num,sys_connect_by_path(a.id,'/')||'/' path,rownum rn from a left join t1 b on b.id=a.id start with pid=0 connect by prior a.id=a.pid order siblings by id)
  3   select c.id,c.name,sum(d.num) from t2 c,t2 d
  4    where d.path like c.path||'%'
  5    group by c.id,c.name,c.rn
  6    order by c.rn asc
  7  ;
 
 ID NAME                 SUM(D.NUM)
--- -------------------- ----------
  1 产品                         34
  2 饮料                         24
  4 可乐                         15
  5 矿泉水                        2
  6 橙汁                          7
  3 食品                         10
  7 牛肉                         10
  8 饼干                          0
  9 挂面                          0
 
9 rows selected
 
SQL> 
一种省略为0的项目,一种没省略,看你需要选用吧.

Answer 6

能把level给带上吗？因为在页面上展示的时候，需要根据level来决定层次关系

Answer 7

SQL> with t1 as (select id,sum(num) num from b group by id),
  2    t2 as (select a.id,a.name,nvl(b.num,0) num,sys_connect_by_path(a.id,'/')||'/' path,rownum rn,level lvl from a left join t1 b on b.id=a.id start with pid=0 connect by prior a.id=a.pid order siblings by id)
  3   select c.id,c.name,c.lvl,sum(d.num) from t2 c,t2 d
  4    where d.path like c.path||'%'
  5    group by c.id,c.name,c.rn,c.lvl
  6    order by c.rn asc
  7  ;
 
 ID NAME                        LVL SUM(D.NUM)
--- -------------------- ---------- ----------
  1 产品                          1         34
  2 饮料                          2         24
  4 可乐                          3         15
  5 矿泉水                        3          2
  6 橙汁                          3          7
  3 食品                          2         10
  7 牛肉                          3         10
  8 饼干                          3          0
  9 挂面                          3          0
 
9 rows selected
 
SQL> 

Answer 8

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结果：
1 1 产品 34
2 2 饮料 24
3 3 食品 10
4 4 可乐 15
5 5 矿泉水 2
6 6 橙汁 7
7 7 牛肉 10

Answer 9

select id,name,sumbytype,length(kk) 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