求逐级向上汇总
--公司信息表
CREATE TABLE TEST_COMPANYINFO
(
COMPID VARCHAR2(30), --公司
COMPNAME VARCHAR2(200), --公司名
PCOMPID VARCHAR2(30) --上级公司
);
--物品信息表
CREATE TABLE TEST_PRODUCTINFO
(
PRODID VARCHAR2(20), --物品
COMPID VARCHAR2(30), --公司
NUM NUMBER(6) --数量
);
INSERT INTO TEST_COMPANYINFO VALUES ('0100','可口可乐广东省公司',NULL);
INSERT INTO TEST_COMPANYINFO VALUES ('0200','可口可乐深圳公司','0100');
INSERT INTO TEST_COMPANYINFO VALUES ('0300','福田区可口可乐分公司','0200');
INSERT INTO TEST_COMPANYINFO VALUES ('0301','南山区可口可乐分公司','0200');
INSERT INTO TEST_COMPANYINFO VALUES ('0302','宝安区可口可乐分分公司','0200');
INSERT INTO TEST_COMPANYINFO VALUES ('0210','可口可乐珠海公司','0100');
INSERT INTO TEST_COMPANYINFO VALUES ('0320','吉大区可口可乐分公司','0210');
INSERT INTO TEST_COMPANYINFO VALUES ('0330','香洲区可口可乐分公司','0210');
INSERT INTO TEST_COMPANYINFO VALUES ('0340','拱北区可口可乐分分公司','0210');
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000001', '0300', 50);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000001', '0301', 60);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000001', '0302', 70);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000002', '0300', 50);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000002', '0301', 60);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000002', '0302', 70);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000002', '0340', 50);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000001', '0320', 60);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000003', '0330', 70);
INSERT INTO TEST_PRODUCTINFO (PRODID, COMPID, NUM)
VALUES ('1000003', '0320', 70);
方法一、select t3.prodid,compid,t3.num,compname from
(select sum(t2.num) num, prodid,pcompid from
(select t1.prodid,compid,t1.num,compname,c.pcompid from(select sum(t.num) num, prodid,pcompid from
(select p.*,pcompid,compname from TEST_COMPANYINFO c,TEST_PRODUCTINFO p where c.compid=p.compid) t
group by prodid,pcompid) t1,TEST_COMPANYINFO c where c.compid=t1.pcompid) t2 group by prodid,pcompid) t3,TEST_COMPANYINFO c
where c.compid=t3.pcompid --省
union
(select t1.prodid,compid,t1.num,compname from(select sum(t.num) num, prodid,pcompid from
(select p.*,pcompid,compname from TEST_COMPANYINFO c,TEST_PRODUCTINFO p where c.compid=p.compid) t
group by prodid,pcompid) t1,TEST_COMPANYINFO c where c.compid=t1.pcompid)--市
union
(select p.*,compname from TEST_COMPANYINFO c,TEST_PRODUCTINFO p where c.compid=p.compid )--区
方法二、
with t as (
select compid,node
from (
select compid,substr(sub_node,instr(sub_node,'/',1,1)+1,instr(sub_node,'/',1,2) - instr(sub_node,'/',1,1) -1) node
from (
select compid,sys_connect_by_path(compid,'/')||'/' sub_node
from test_companyinfo
connect by compid = prior pcompid
)
)
where node in (
select distinct compid
from test_companyinfo
where connect_by_isleaf = 1
connect by prior compid = pcompid
)
)
select t.compid,(select compname from test_companyinfo where compid= t.compid) compname,
b.prodid,
sum(b.num)
from t,test_productinfo b
where t.node = b.compid
group by t.compid,b.prodid
order by t.compid,b.prodid
具体参见图示：
方法三、树结构查询
select a.*,b.num ,
case when sum(num) over(partition by a.compid,compname,pcompid order by null) is null
then sum(nvl(num,0)) over(partition by decode(level,2,a.compid,pcompid) order by a.compid desc) +
decode(level,1,sum(nvl(num,0)) over(partition by null order by null),0)
else sum(num) over(partition by a.compid,compname,pcompid order by null)
end
from TEST_COMPANYINFO a left join TEST_PRODUCTINFO b on a.compid=b.compid
start with pcompid is null
CONNECT BY PCOMPID = PRIOR a.COMPID
方法四、树状结构查询
WITH v_base_ma AS ( SELECT prodid pid, compid cid, SUM (num) ma
FROM TEST_PRODUCTINFO
GROUP BY prodid, compid
HAVING SUM (num) > 0),
v_comp
AS (SELECT DISTINCT COMPID cid, COMPNAME cname FROM TEST_COMPANYINFO),
v_prod AS (SELECT DISTINCT prodid pid FROM TEST_PRODUCTINFO),
OD AS (
SELECT COMPID,LEVEL LV
FROM TEST_COMPANYINFO
START WITH TRIM(PCOMPID) IS NULL
CONNECT BY PCOMPID = PRIOR COMPID)
SELECT A.*,LV
FROM (SELECT a.cid,
a.cname,
a.pid,
ma
FROM (SELECT a.cid,
a.cname,
b.pid,
(SELECT NVL (SUM (ma), 0) ma
FROM v_base_ma t
WHERE t.pid = b.pid
AND t.cid IN
( SELECT COMPID
FROM TEST_COMPANYINFO
START WITH PCOMPID = a.cid
CONNECT BY PCOMPID = PRIOR COMPID))
ma
FROM v_comp a, v_prod b) a
WHERE ma > 0
UNION ALL
SELECT l.cid,
cname,
pid,
ma
FROM v_base_ma l, v_comp e
WHERE l.cid = e.cid) A ,OD
WHERE CID=OD.COMPID
ORDER BY LV,cid, pid