一条sql搞定递归向上汇总

1. 根据末级递归查询出上级，形成树形结构

SELECT DISTINCT ACC.ACCOUNTCODEID, ACC.PARENTCODEID, ACC.ACCOUNTCODE, ACC.ACCOUNTCODENAME, 
 ACC.FIRSTORDER, ACC.SECONDORDER,T.PLANMAINID, T.PLANMAINNAME, T.PLANMAINCODE,'Y' AS ISACCOUNT,
CASE WHEN NVL(T.ACCOUNTCODEID,0) =0 THEN 'Y' ELSE 'N' END AS ISADD,
T.COMPANYID, T.BUDGETYEAR, 
T.NAMTMONTH1,T.NAMTMONTH2,T.NAMTMONTH3,T.NAMTMONTH4,T.NAMTMONTH5,T.NAMTMONTH6,T.NAMTMONTH7,T.NAMTMONTH8,
T.NAMTMONTH9,T.NAMTMONTH10,T.NAMTMONTH11,T.NAMTMONTH12
FROM FM_ACCOUNTCODE ACC
LEFT JOIN (
SELECT COMPANYID, BUDGETYEAR, ACCOUNTCODEID, ACCOUNTCODE , ACCOUNTCODENAME, 0 AS PLANMAINID, NULL AS PLANMAINNAME,NULL AS PLANMAINCODE,
SUM(NAMTMONTH1) AS NAMTMONTH1,SUM(NAMTMONTH2) AS NAMTMONTH2,SUM(NAMTMONTH3) AS NAMTMONTH3,
SUM(NAMTMONTH4) AS NAMTMONTH4,SUM(NAMTMONTH5) AS NAMTMONTH5,SUM(NAMTMONTH6) AS NAMTMONTH6,
SUM(NAMTMONTH7) AS NAMTMONTH7,SUM(NAMTMONTH8) AS NAMTMONTH8,SUM(NAMTMONTH9) AS NAMTMONTH9,
SUM(NAMTMONTH10) AS NAMTMONTH10,SUM(NAMTMONTH11) AS NAMTMONTH11,SUM(NAMTMONTH12) AS NAMTMONTH12
FROM FM_CURRENTEFFECTIBUDGET WHERE COMPANYID = 6205 AND RECORDSTATUS <> 'INACTIVE'
--AND ISACCOUNTBUDGET = 'Y'
GROUP BY COMPANYID, BUDGETYEAR, ACCOUNTCODEID, ACCOUNTCODE , ACCOUNTCODENAME--,PLANMAINID, PLANMAINNAME, PLANMAINCODE
) T ON ACC.ACCOUNTCODEID = T.ACCOUNTCODEID
START WITH ACC.ACCOUNTCODEID = T.ACCOUNTCODEID
CONNECT BY PRIOR  ACC.PARENTCODEID = ACC.ACCOUNTCODEID
ORDER BY ACC.FIRSTORDER, ACC.SECONDORDER

2.根据1的sql，我们可以轻松得到所有层级的数据，但是呢，父级上并没有进行汇总下级的金额，所以改造为：

SELECT 
ACCOUNTCODEID, PARENTCODEID, ACCOUNTCODE, ACCOUNTCODENAME, FIRSTORDER, SECONDORDER,
PLANMAINID, PLANMAINNAME, PLANMAINCODE, ISACCOUNT,ISADD,
COMPANYID, BUDGETYEAR, 
SUM(NAMTMONTH1) AS NAMTMONTH1,
SUM(NAMTMONTH2) AS NAMTMONTH2,
SUM(NAMTMONTH3) AS NAMTMONTH3,
SUM(NAMTMONTH4) AS NAMTMONTH4,
SUM(NAMTMONTH5) AS NAMTMONTH5,
SUM(NAMTMONTH6) AS NAMTMONTH6,
SUM(NAMTMONTH7) AS NAMTMONTH7,
SUM(NAMTMONTH8) AS NAMTMONTH8,
SUM(NAMTMONTH9) AS NAMTMONTH9,
SUM(NAMTMONTH10) AS NAMTMONTH10,
SUM(NAMTMONTH11) AS NAMTMONTH11,
SUM(NAMTMONTH12) AS NAMTMONTH12
FROM (
SELECT CONNECT_BY_ROOT ACCOUNTCODEID ACCOUNTCODEID,CONNECT_BY_ROOT PARENTCODEID PARENTCODEID,
CONNECT_BY_ROOT ACCOUNTCODE ACCOUNTCODE,CONNECT_BY_ROOT ACCOUNTCODENAME ACCOUNTCODENAME,
CONNECT_BY_ROOT FIRSTORDER FIRSTORDER,CONNECT_BY_ROOT SECONDORDER SECONDORDER,
CONNECT_BY_ROOT COMPANYID COMPANYID,CONNECT_BY_ROOT BUDGETYEAR BUDGETYEAR,
CONNECT_BY_ROOT PLANMAINID PLANMAINID,CONNECT_BY_ROOT PLANMAINNAME PLANMAINNAME,
CONNECT_BY_ROOT PLANMAINCODE PLANMAINCODE,CONNECT_BY_ROOT ISACCOUNT ISACCOUNT,
CONNECT_BY_ROOT ISADD ISADD,
A.NAMTMONTH1,A.NAMTMONTH2,A.NAMTMONTH3,A.NAMTMONTH4,A.NAMTMONTH5,A.NAMTMONTH6,A.NAMTMONTH7,A.NAMTMONTH8,
A.NAMTMONTH9,A.NAMTMONTH10,A.NAMTMONTH11,A.NAMTMONTH12
FROM (
SELECT DISTINCT ACC.ACCOUNTCODEID, ACC.PARENTCODEID, ACC.ACCOUNTCODE, ACC.ACCOUNTCODENAME, 
 ACC.FIRSTORDER, ACC.SECONDORDER,T.PLANMAINID, T.PLANMAINNAME, T.PLANMAINCODE,'Y' AS ISACCOUNT,
CASE WHEN NVL(T.ACCOUNTCODEID,0) =0 THEN 'Y' ELSE 'N' END AS ISADD,
T.COMPANYID, T.BUDGETYEAR, 
T.NAMTMONTH1,T.NAMTMONTH2,T.NAMTMONTH3,T.NAMTMONTH4,T.NAMTMONTH5,T.NAMTMONTH6,T.NAMTMONTH7,T.NAMTMONTH8,
T.NAMTMONTH9,T.NAMTMONTH10,T.NAMTMONTH11,T.NAMTMONTH12
FROM FM_ACCOUNTCODE ACC
LEFT JOIN (
SELECT COMPANYID, BUDGETYEAR, ACCOUNTCODEID, ACCOUNTCODE , ACCOUNTCODENAME, 0 AS PLANMAINID, NULL AS PLANMAINNAME,NULL AS PLANMAINCODE,
SUM(NAMTMONTH1) AS NAMTMONTH1,SUM(NAMTMONTH2) AS NAMTMONTH2,SUM(NAMTMONTH3) AS NAMTMONTH3,
SUM(NAMTMONTH4) AS NAMTMONTH4,SUM(NAMTMONTH5) AS NAMTMONTH5,SUM(NAMTMONTH6) AS NAMTMONTH6,
SUM(NAMTMONTH7) AS NAMTMONTH7,SUM(NAMTMONTH8) AS NAMTMONTH8,SUM(NAMTMONTH9) AS NAMTMONTH9,
SUM(NAMTMONTH10) AS NAMTMONTH10,SUM(NAMTMONTH11) AS NAMTMONTH11,SUM(NAMTMONTH12) AS NAMTMONTH12
FROM FM_CURRENTEFFECTIBUDGET WHERE COMPANYID = 6205 AND RECORDSTATUS <> 'INACTIVE'
--AND ISACCOUNTBUDGET = 'Y'
GROUP BY COMPANYID, BUDGETYEAR, ACCOUNTCODEID, ACCOUNTCODE , ACCOUNTCODENAME--,PLANMAINID, PLANMAINNAME, PLANMAINCODE
) T ON ACC.ACCOUNTCODEID = T.ACCOUNTCODEID
START WITH ACC.ACCOUNTCODEID = T.ACCOUNTCODEID
CONNECT BY PRIOR  ACC.PARENTCODEID = ACC.ACCOUNTCODEID
ORDER BY ACC.FIRSTORDER, ACC.SECONDORDER
) A
CONNECT BY PRIOR ACCOUNTCODEID = PARENTCODEID
)
GROUP BY ACCOUNTCODEID, PARENTCODEID, ACCOUNTCODE, ACCOUNTCODENAME, FIRSTORDER, SECONDORDER,
COMPANYID, BUDGETYEAR,PLANMAINID, PLANMAINNAME, PLANMAINCODE,ISACCOUNT,ISADD ORDER BY FIRSTORDER, SECONDORDER

此文章仅作为笔者工作中遇到的问题记录，同时希望能帮助到其他同学