求逐级向上汇总

摘要:
逐级汇总--公司信息表CREATE table TEST_ COMPANYINFO(COMPADVARCHAR2(30),--公司COMPANAMEVARCHAR 2(200),--企业名称PCCOMPADVARCHAR1(30)--母公司)--项目信息表CREATETABLETEST_ PRODUCTINFO(PRODIDVARCHAR2(20),--项目COMPIDARCHAR2

求逐级向上汇总

--公司信息表

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

具体参见图示:

求逐级向上汇总第1张

方法三、树结构查询

select a.*,b.num  ,

case when sum(numover(partition by a.compid,compname,pcompid order by nullis 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(numover(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

免责声明:文章转载自《求逐级向上汇总》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇oracle的一些函数、指令win7下安装IIS7.0及部署VS2010 ASP.NET程序网站的相关问题下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

306. 累加数

累加数是一个字符串,组成它的数字可以形成累加序列。 一个有效的累加序列必须至少包含 3 个数。除了最开始的两个数以外,字符串中的其他数都等于它之前两个数相加的和。 给定一个只包含数字'0'-'9'的字符串,编写一个算法来判断给定输入是否是累加数。 说明:累加序列里的数不会以 0 开头,所以不会出现1, 2, 03 或者1, 02, 3的情况。 示例 1: 输...

redis----java操作redis

添加jar包 <dependency> <groupId>redis.clients</groupId> <artifactId>jedis</artifactId> <version>3.0.1&...

[HDU1017]Exact cover[DLX][Dancing Links详解][注释例程学习法]

Dancing Links解决Exact Cover问题. 用到了循环双向十字链表. dfs. 论文一知半解地看了一遍,搜出一篇AC的源码,用注释的方法帮助理解. HIT ACM 感谢源码po主.链接如下: http://blog.csdn.net/yysdsyl/article/details/4266876 #include <iostream...

《T-GCN: A Temporal Graph Convolutional Network for Traffic Prediction》 代码解读

论文链接:https://arxiv.org/abs/1811.05320 博客原作者Missouter,博客链接https://www.cnblogs.com/missouter/,欢迎交流。 解读了一下这篇论文github上关于T-GCN的代码,主要分为main文件与TGCN文件两部分,后续有空将会更新其他部分作为baseline代码的解读(鸽)。 1、...

回调函数高级

源码: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type"content="t...

中南大学第一届长沙地区程序设计邀请赛 To Add Which?

1350: To Add Which? Time Limit:1 SecMemory Limit:128 MB Description There is an integer sequence withNintegers. You can use 1 unit of cost to increase any integer in the sequence...