Oracle sql 学习笔记1 利用rollup和connect by实现报表统计

在日常收入报表统计中，通常用到机构代码、行业代码、征收项目代码等树形结构表单。比如：分单位分级次统计收入完成情况、分sz情况统计表、分单位分sz统计收入完成情况、分行业统计收入完成情况等。本文虚拟了5张表模拟其中两种情形进行介绍和说明。

一、数据准备

假设有如下五张表，分别是:tab1_jks,tab2_hy,tab3_sz,tab4_swjg,tab5_ysfpbl。
1.创建测试表tab1_jks

create table tab1_jks
(hy_id VARCHAR2(4) not null,sz_id VARCHAR2(5) not null,rkse number(12,2),ssswjg_id VARCHAR2(11));

2.创建测试表tab2_hy

create table tab2_hy
(hy_id varchar2(4) PRIMARY key,hy_name varchar2(70) not null,sjhy_id varchar(4));

3.创建测试表tab3

create table tab3_sz
(sz_id VARCHAR2(5) primary key,sz_name varchar(150) not null,sjsz_id varchar2(5) );

4.创建测试表tab4_swjg

create table tab4_swjg
(swjg_id char(11) primary key,swjg_name varchar(150) not null,sjswjg_id varchar(11);

5.创建测试表tab5_ysfpbl

create table tab5_ysfpbl
(
ysfpbl_id char(8) primary key,
ysfpbl_name varchar2(99) not null,
zyj_bl number,
ssj_bl number,
dsj_bl number,
qxj_bl number
);

6.分别插入测试表数据。
此处省略。

二、分单位分级次统计收入完成情况

主要有两种实现方式，体现了不同的思路，运用了不同的方法：
（一）采用rollup，结合grouping产生汇总行；再将汇总后的数据与格式化后机构职能树进行连接。树形结构层级较少时，使用这种方式较为简便。
主要有以下3个步骤：

1. 第一步，将缴款书数据按ysfpbl进行换算，分别计算出各预算级次收入，同时按ssswjg进行聚合汇总。
2. 第二步，将机构职能树进行格式化，按机构层级进行展示。
3. 第三步，将前两步的结果集按进行连接，并格式化输出结果。

SELECT B.SWJGMC "单位",
       CASE
         WHEN A.HEJI = 0 THEN
          NULL
         ELSE
          TO_CHAR(A.HEJI, '999,999,999.99')
       END 本月,
       CASE
         WHEN A.ZYJ = 0 THEN
          NULL
         ELSE
          TO_CHAR(A.ZYJ, '999,999,999.99')
       END 中央级,
       CASE
         WHEN A.SSJ = 0 THEN
          NULL
         ELSE
          TO_CHAR(A.SSJ, '999,999,999.99')
       END 省级,
       CASE
         WHEN A.DSJ = 0 THEN
          NULL
         ELSE
          TO_CHAR(A.DSJ, '999,999,999.99')
       END 地市级,
       CASE
         WHEN A.QXJ = 0 THEN
          NULL
         ELSE
          TO_CHAR(A.QXJ, '999,999,999.99')
       END 区县级
  FROM (SELECT CASE
                 WHEN GROUPING(T.SSSWJG_ID) = 1 THEN
                  '15117000000'
                 ELSE
                  T.SSSWJG_ID
               END SWJG_ID, --若为汇总行，则将当前行skssswjg_dm置为'15117000000'。
               SUM(T.RKSE) HEJI, --合计
               SUM(T.RKSE * P.ZYJ_BL) ZYJ, --中央级
               SUM(T.RKSE * P.SSJ_BL) SSJ, --省市级
               SUM(T.RKSE * P.DSJ_BL) DSJ, --地市级
               SUM(T.RKSE * P.QXJ_BL) QXJ --区县级
          FROM TAB1_JKS T --缴款书
          JOIN TAB5_YSFPBL P --预算分配比例
            ON T.YSFPBL_ID = P.YSFPBL_ID
          JOIN TAB3_SZ M --征收项目代码表
            ON M.SZ_ID = T.SZ_ID
         WHERE M.SJSZ_ID = '10100' --只统计某类收入。
         GROUP BY ROLLUP(T.SSSWJG_ID) --使用rollup，增加汇总行。
         ORDER BY T.SSSWJG_ID) A --1.将缴款书数据按ysfpbl进行换算，分别计算出各预算级次收入，同时按skssswjg进行聚合汇总。
  JOIN
 (SELECT T.SWJG_ID,
         LPAD(' ', (LEVEL - 1) * 4) || T.SWJG_NAME SWJGMC, --格式化机构名称。
         LEVEL LEVEL_ID,
         ROWNUM RN --取序号
    FROM TAB4_SWJG T
   START WITH T.SWJG_ID = '15117000000'
  CONNECT BY PRIOR T.SWJG_ID = T.SJSWJG_ID--2.将机构职能树进行格式化，按机构层级进行展示。
  ) B 
    ON A.SWJG_ID = B.SWJG_ID
 ORDER BY B.RN;--3.将前两步的结果集按进行连接，并格式化输出结果。

（二）利用树形结构直接将数据映射至各层级机构代码，再按机构代码分组聚合产生汇总行。
主要有以下6个步骤：

1. 将机构职能树的每一级机关与其所有上级进行分别对应，包括自己。
2. 对tab1_jks按sk所属机关进行分组汇总（此处未使用rollup)。
3. 将加工后的缴款书信息按机构代码与机构职能树进行关联。
4. 将连接后的缴款书数据按fid进行聚合，求出各级机构的合计行（树形结构层级较多时，建议采用这种方式）。
5. 将机构职能树进行格式化。
6. 将第4步和第5步的结果集按swjg_id进行连接，并格式化输出树形结果。

SELECT E.SWJGMC "单位",
       TO_CHAR(D.HEJI, '999,999,999.99') "本月",
       TO_CHAR(D.ZYJ, '999,999,999.99') "中央级",
       TO_CHAR(D.SSJ, '999,999,999.99') "省级",
       TO_CHAR(D.DSJ, '999,999,999.99') "市级",
       TO_CHAR(D.QXJ, '999,999,999.99') "县级"
  FROM (SELECT C.FID,
               SUM(C.HEJI) HEJI,
               SUM(C.ZYJ) ZYJ,
               SUM(C.SSJ) SSJ,
               SUM(C.DSJ) DSJ,
               SUM(C.QXJ) QXJ
          FROM (SELECT B.ID,
                       B.FID,
                       O.HEJI,
                       O.ZYJ,
                       O.SSJ,
                       CASE
                         WHEN O.DSJ = 0 THEN
                          NULL
                         ELSE
                          O.DSJ
                       END DSJ,
                       CASE
                         WHEN O.QXJ = 0 THEN
                          NULL
                         ELSE
                          O.QXJ
                       END QXJ,
                       ROWNUM RN
                  FROM (SELECT T.SSSWJG_ID SWJG_ID,
                               SUM(T.RKSE) HEJI, --合计
                               SUM(T.RKSE * P.ZYJ_BL) ZYJ, --中央级
                               SUM(T.RKSE * P.SSJ_BL) SSJ, --省市级
                               SUM(T.RKSE * P.DSJ_BL) DSJ, --地市级
                               SUM(T.RKSE * P.QXJ_BL) QXJ  --区县级
                          FROM TAB1_JKS T
                          JOIN TAB5_YSFPBL P
                            ON T.YSFPBL_ID = P.YSFPBL_ID
                          JOIN TAB3_SZ R
                            ON T.SZ_ID = R.SZ_ID
                         WHERE TO_CHAR(T.RKJZRQ, 'yyyymm') = '202002'
                           AND R.SJSZ_ID = '10100'
                         GROUP BY T.SSSWJG_ID) O --2.对tab1_jks按所属机构id进行分组汇总
                  JOIN
                
                 (SELECT T.SWJG_ID ID, CONNECT_BY_ROOT(T.SWJG_ID) FID
                   FROM TAB4_SWJG T
                 CONNECT BY PRIOR T.SWJG_ID = T.SJSWJG_ID
                  ORDER SIBLINGS BY T.SWJG_ID 
                 ) B --1.将机构职能树每个部门与其所有上级机关进行一一对应，包括自己。
                    ON O.SWJG_ID = B.ID) C --3.将加工后的缴款书信息按机构代码与机构职能树进行关联。
         GROUP BY C.FID) D --4.将连接后的缴款书数据按fid进行聚合，求出上级机构的合计行。
  JOIN (SELECT T.SWJG_ID ID,
               LPAD(' ', (LEVEL - 1) * 4) || T.SWJG_NAME SWJGMC,
               T.SJSWJG_ID FID,
               LEVEL,
               ROWNUM RN
          FROM TAB4_SWJG T
         START WITH T.SWJG_ID = '15117000000'
        CONNECT BY PRIOR T.SWJG_ID = T.SJSWJG_ID
         ORDER SIBLINGS BY T.SWJG_ID) E --5.将机构职能树进行格式化。
    ON E.ID = D.FID --6.将聚合后的缴款书信息与格式化后的机构职能树进行连接，并进行格式化输出。
 ORDER BY E.RN;

以上两种方式，可视树形结构层级多少选择使用。其最终输出结果相同：

在上述两种方式中，connect by（递归查询）共使用了两种不同查询，分别有不同的作用和效果，具体将在下篇记录中详细介绍。

三、分行业统计收入完成情况

要完成分行业情况统计表，需要使用到tab2_hy这张树形结构表，其中有hy_id和sjhy_id两个字段，sjhy_id是hy_id的fid，共有4个层级，在输出结果时，每个层级都应有汇总行数据。

主要有以下5个步骤：

1. 对tab1_jks数据进行加工整理，按hy_id进行分组聚合。
2. 对tab2_hy进行加工整理，将每一个行业代码与其所有上级行业代码进行对应，包括自己。
3. 将前面两个步骤的结果集按hy_id进行关联，将将关联后的数据按fid进行分组聚合，形成每个行业层级的汇总行数据。
4. 对tab2_hy进行加工，按行业代码层级关系展示树形层级结构。
5. 将前3步操作后的结果集与第4步操作形成的树形层级表进行关联，展示行业代码表树形层级关系，最终输出报表。

SELECT D.HYMC 行业名称, C.SJJE 金额, D.LEVEL_ID 层级代码
  FROM (SELECT B.FID FID, SUM(A.SJJE) SJJE
          FROM (SELECT T.HY_ID ID, SUM(T.RKSE) SJJE
                  FROM TAB1_JKS T
                  JOIN TAB3_SZ R
                    ON R.SZ_ID = T.SZ_ID
                 WHERE TO_CHAR(T.RKJZRQ, 'yyyymm') = '202002'
                   AND R.SJSZ_ID = '10100'
                 GROUP BY T.HY_ID) A --1.加工整理zs_jks
          JOIN (SELECT T.HY_ID ID, CONNECT_BY_ROOT(T.HY_ID) FID
                 FROM TAB2_HY T
               CONNECT BY PRIOR T.HY_ID = SJHY_ID) B --2.加工整理tab2_hy，将每一个行业代码与其所有上级行业代码进行对应，包括自己。
            ON A.ID = B.ID
         GROUP BY B.FID) C --3.将前面两个步骤的结果集按hy_id进行关联，将将关联后的数据按fid进行分组聚合，形成每个行业层级的汇总行数据。
  JOIN (SELECT T.HY_ID ID,
               LPAD(' ', (LEVEL - 1) * 4) || T.HY_NAME HYMC,
               LEVEL LEVEL_ID,
               ROWNUM RN
          FROM TAB2_HY T
         START WITH T.SJHY_ID IS NULL
        CONNECT BY PRIOR T.HY_ID = T.SJHY_ID) D --4.对tab2_hy进行加工，按行业代码层级关系展示树形层级结构。
    ON D.ID = C.FID
 ORDER BY D.RN --5.将前3步操作后的结果集与第4步操作形成的树形层级表进行关联，展示行业代码表树形层级关系，最终输出报表。

输出 层级代码字段，是方便对报表按行业层级进行数据筛选。
思考：如何使用rollup在结果输出表中增加全表汇总行，并保持树形层级结构？