树形关系记录遍历

来源：互联网发布：无损音乐播放软件编辑：程序博客网时间：2024/06/07 04:02

基础知识：

ORACLE查询中，存在一些不显示于查询结果中的伪字段；

科目有层级关系，上层级的金额是下层级金额的汇总；

START WITH 指定根节点，CONNECT_BY用于子父连接，prior修饰子\父记录来确定是向下\上遍历，伪字段CONNECT_BY_ISLEAF为1表明该记录为叶子节点记录，CONNECT_BY_ROOT(当前节点)函数返回当前节点的根结点，CONNECY_BY_PATH(当前节点，分割符)可以返回以分割符分割的从根节点到当前节点的路径。

WITH SUBSELECT AS (       SELECT COD_CONTO_ELEGER,CONNECT_BY_ROOT(COD_CONTO_ELEGER) TREE_ROOT ,CONNECT_BY_ISLEAF ISLEAF,SYS_CONNECT_BY_PATH(COD_CONTO_ELEGER,'->') PATH       FROM (         SELECT COD_CONTO_ELEGER,COD_CONTO_ELEGER_PADRE          FROM CONTO_GERARCHIA WHERE COD_CONTO_GERARCHIA='01'       )        START WITH COD_CONTO_ELEGER  IN ('100100','110100')       CONNECT BY PRIOR COD_CONTO_ELEGER = COD_CONTO_ELEGER_PADRE )SELECT TREE_ROOT, ISLEAF,PATH,SUM((CASE WHEN TREE_ROOT IN ('110100') THEN importo *-1 ELSE importo END) )importoFROM CONTO_GERARCHIA_ABBI CILEFT JOIN ( SELECT COD_CONTO_ELEGER,TREE_ROOT, ISLEAF,PATH FROM SUBSELECT ) T ON T.COD_CONTO_ELEGER = CI.COD_CONTO_ELEGERleft join (select cod_conto,importo from DATI_SALDI_LORDI A WHERE A.COD_AZIENDA IN ('9000') AND A.COD_SCENARIO = '2016ACT' AND A.COD_PERIODO = '02') g  on CI.cod_conto=g.cod_contoWHERE CI.COD_CONTO_ELEGER IN (SELECT COD_CONTO_ELEGER FROM SUBSELECT ) GROUP BY TREE_ROOT, ISLEAF,PATHORDER BY TREE_ROOT

查询结果;

    TREE_ROOT   ISLEAF   PATH                      IMPORTO1    100100      0       ->100100 2    100100      1       ->100100->100100F10          03    100100      1       ->100100->100100F20 575162631.664    100100  1  ->100100->100100F30 3307700706.365    110100  0       ->1101006    110100  1  ->110100->110100F10 -200000000

应用：

查询某月科目余额的累计数，以sum函数累计分区中的数据时，发现层级多一级，金额会翻一倍。

解决方案：

只取指定最低层级数据的叶子节点，最后利用查找表影射叶子节点到一级科目，并求和（因为有些子科目是不需要的，不然可直接取一级科目）。

with  cc as(      select fyear,lpad(cast(fperiod  AS VARCHAR(2)),2,'0')  fperiod,subjcode,subjname,subjlev,companycode,companyname, beginbalance,endbalance,localdebitamount, localcreditamount      from ODS.ODSTTKD_v_MR_BALANCE@FMSLK        where fcurrencyid = 1 and fdetailid = 0),S1 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'01' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '01' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '01' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '01'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S2 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'02' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '02' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '02' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '02'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S3 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'03' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '03' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '03' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '03'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S4 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'04' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '04' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '04' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '04'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S5 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'05' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '05' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '05' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '05'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S6 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'06' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '06' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '06' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '06'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S7 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'07' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '07' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '07' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '07'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S8 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'08' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '08' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '08' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '08'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S9 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'09' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '09' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '09' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '09'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S10 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'10' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '10' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '10' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '10'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S11 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'11' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '11' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '11' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '11'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE),S12 AS (  SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,         SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount  FROM(      SELECT fyear,'11' fperiod,subjcode,subjname,subjlev,companycode,companyname,             CASE FPERIOD WHEN '12' THEN  beginbalance ELSE 0 END beginbalance,             CASE FPERIOD WHEN '12' THEN  endbalance ELSE 0 END endbalance,             SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount      FROM(          select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,                sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount          from cc          WHERE FPERIOD  <= '12'          group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname          ORDER BY FYEAR,SUBJCODE      )       group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance  )  group by  fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname  ORDER BY FYEAR,SUBJCODE)select    YEARMONTH,fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance,              SHamount,SHMARK, CLIENT from (select  YEARMONTH, fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance,              SHamount,SHMARK, CLIENT    from (select  SUBJP,SUBJI, fyear||fperiod YEARMONTH, fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance,              SHamount,SHMARK, CLIENT    from (      SELECT   companycode||fyear||lpad(cast(fperiod  AS VARCHAR(2)),2,'0')||SUBSTR( subjcode,1,DECODE(subjlev,1,NULL,2,4,(subjlev -2)*3+4)) SUBJP,              companycode||fyear||lpad(cast(fperiod  AS VARCHAR(2)),2,'0')||subjcode  SUBJI,              fyear,fperiod,subjcode,subjname,subjlev,M.ELEDIM_OUTPUT1  companycode,companyname,beginbalance,endbalance,               localcreditamount*-1 SHamount,'H' SHMARK,'K3' CLIENT      FROM (            SELECT * FROM S1            UNION ALL            SELECT * FROM S2            UNION ALL            SELECT * FROM S3            UNION ALL            SELECT * FROM S4            UNION ALL            SELECT * FROM S5            UNION ALL            SELECT * FROM S6            UNION ALL            SELECT * FROM S7            UNION ALL            SELECT * FROM S8            UNION ALL            SELECT * FROM S9            UNION ALL            SELECT * FROM S10            UNION ALL            SELECT * FROM S11            UNION ALL            SELECT * FROM S12      )      S  LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_BUKRS_K3' AND M.COD_MAPPATURA = 'HI_DATA_GL' AND M.ELEDIM_INPUT2 = S.companycode      )     WHERE CONNECT_BY_ISLEAF = 1    start with SUBJLEV = 1    connect by prior SUBJI =  SUBJP )union all select  YEARMONTH, fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance,              SHamount,SHMARK, CLIENT    from (select  SUBJP,SUBJI, fyear||fperiod YEARMONTH, fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance,              SHamount,SHMARK, CLIENTFROM (SELECT  companycode||fyear||lpad(cast(fperiod  AS VARCHAR(2)),2,'0')||SUBSTR( subjcode,1,DECODE(subjlev,1,NULL,2,4,(subjlev -2)*3+4)) SUBJP,        companycode||fyear||lpad(cast(fperiod  AS VARCHAR(2)),2,'0')||subjcode  SUBJI,        fyear,fperiod,subjcode,subjname,subjlev,M.ELEDIM_OUTPUT1  companycode,companyname,0 beginbalance,0 endbalance,         localdebitamount SHamount,'S' SHMARK,'K3' CLIENTFROM (      SELECT * FROM S1      UNION ALL      SELECT * FROM S2      UNION ALL      SELECT * FROM S3      UNION ALL      SELECT * FROM S4      UNION ALL      SELECT * FROM S5      UNION ALL      SELECT * FROM S6      UNION ALL      SELECT * FROM S7      UNION ALL      SELECT * FROM S8      UNION ALL      SELECT * FROM S9      UNION ALL      SELECT * FROM S10      UNION ALL      SELECT * FROM S11      UNION ALL      SELECT * FROM S12      )      S  LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_BUKRS_K3' AND M.COD_MAPPATURA = 'HI_DATA_GL' AND M.ELEDIM_INPUT2 = S.companycode     )     WHERE CONNECT_BY_ISLEAF = 1    start with SUBJLEV = 1    connect by prior SUBJI =  SUBJP     ))where companycode IN ({IN-ENTITY}) AND YEARMONTH IN ({IN-YEARMONTH})

阅读全文

0 0