WITH T AS
(SELECT '10000000' ID, 'China' PART, NULL PID, 'VI' P_CODE, 10 NUM
FROM DUAL
UNION ALL
SELECT '10000000' ID, 'China' PART, NULL PID, 'DS' P_CODE, 20 NUM
FROM DUAL
UNION ALL
SELECT '11000000' ID, 'Beijin' PART, '10000000' PID, 'VI' P_CODE, 5 NUM
FROM DUAL
UNION ALL
SELECT '11000000' ID, 'Beijin' PART, '10000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '12000000' ID, 'Shanghai' PART, '10000000' PID, 'VI' P_CODE, 2 NUM
FROM DUAL
UNION ALL
SELECT '12000000' ID, 'Shanghai' PART, '10000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '11000001' ID, 'Caoyang' PART, '11000000' PID, 'VI' P_CODE, 5 NUM
FROM DUAL
UNION ALL
SELECT '11000002' ID, 'Huairou' PART, '11000000' PID, 'VI' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '11000002' ID, 'Huairou' PART, '11000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
)
SELECT R_ID, R_PART, R_P_CODE, SUM(NUM)
FROM (SELECT CONNECT_BY_ROOT(T.ID) R_ID,
CONNECT_BY_ROOT(T.PART) R_PART,
CONNECT_BY_ROOT(P_CODE) R_P_CODE,
T.NUM
FROM T
START WITH ID = '10000000'
CONNECT BY T.PID = PRIOR ID
AND T.P_CODE = PRIOR P_CODE)
GROUP BY R_ID, R_PART, R_P_CODE;
(SELECT '10000000' ID, 'China' PART, NULL PID, 'VI' P_CODE, 10 NUM
FROM DUAL
UNION ALL
SELECT '10000000' ID, 'China' PART, NULL PID, 'DS' P_CODE, 20 NUM
FROM DUAL
UNION ALL
SELECT '11000000' ID, 'Beijin' PART, '10000000' PID, 'VI' P_CODE, 5 NUM
FROM DUAL
UNION ALL
SELECT '11000000' ID, 'Beijin' PART, '10000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '12000000' ID, 'Shanghai' PART, '10000000' PID, 'VI' P_CODE, 2 NUM
FROM DUAL
UNION ALL
SELECT '12000000' ID, 'Shanghai' PART, '10000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '11000001' ID, 'Caoyang' PART, '11000000' PID, 'VI' P_CODE, 5 NUM
FROM DUAL
UNION ALL
SELECT '11000002' ID, 'Huairou' PART, '11000000' PID, 'VI' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '11000002' ID, 'Huairou' PART, '11000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
)
SELECT R_ID, R_PART, R_P_CODE, SUM(NUM)
FROM (SELECT CONNECT_BY_ROOT(T.ID) R_ID,
CONNECT_BY_ROOT(T.PART) R_PART,
CONNECT_BY_ROOT(P_CODE) R_P_CODE,
T.NUM
FROM T
START WITH ID = '10000000'
CONNECT BY T.PID = PRIOR ID
AND T.P_CODE = PRIOR P_CODE)
GROUP BY R_ID, R_PART, R_P_CODE;
(SELECT '10000000' ID, 'China' PART, NULL PID, 'VI' P_CODE, 10 NUM
FROM DUAL
UNION ALL
SELECT '10000000' ID, 'China' PART, NULL PID, 'DS' P_CODE, 20 NUM
FROM DUAL
UNION ALL
SELECT '11000000' ID, 'Beijin' PART, '10000000' PID, 'VI' P_CODE, 5 NUM
FROM DUAL
UNION ALL
SELECT '11000000' ID, 'Beijin' PART, '10000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '12000000' ID, 'Shanghai' PART, '10000000' PID, 'VI' P_CODE, 2 NUM
FROM DUAL
UNION ALL
SELECT '12000000' ID, 'Shanghai' PART, '10000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '11000001' ID, 'Caoyang' PART, '11000000' PID, 'VI' P_CODE, 5 NUM
FROM DUAL
UNION ALL
SELECT '11000002' ID, 'Huairou' PART, '11000000' PID, 'VI' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '11000002' ID, 'Huairou' PART, '11000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
)
SELECT R_ID, R_PART, R_P_CODE, SUM(NUM)
FROM (SELECT CONNECT_BY_ROOT(T.ID) R_ID,
CONNECT_BY_ROOT(T.PART) R_PART,
CONNECT_BY_ROOT(P_CODE) R_P_CODE,
T.NUM
FROM T
START WITH ID = '10000000'
CONNECT BY T.PID = PRIOR ID
AND T.P_CODE = PRIOR P_CODE)
GROUP BY R_ID, R_PART, R_P_CODE;能给全些吗,上半部分是什么意思啊。
(SELECT '10000000' ID, 'China' PART, NULL PID, 'VI' P_CODE, 10 NUM
FROM DUAL
UNION ALL
SELECT '10000000' ID, 'China' PART, NULL PID, 'DS' P_CODE, 20 NUM
FROM DUAL
UNION ALL
SELECT '11000000' ID, 'Beijin' PART, '10000000' PID, 'VI' P_CODE, 5 NUM
FROM DUAL
UNION ALL
SELECT '11000000' ID, 'Beijin' PART, '10000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '12000000' ID, 'Shanghai' PART, '10000000' PID, 'VI' P_CODE, 2 NUM
FROM DUAL
UNION ALL
SELECT '12000000' ID, 'Shanghai' PART, '10000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '11000001' ID, 'Caoyang' PART, '11000000' PID, 'VI' P_CODE, 5 NUM
FROM DUAL
UNION ALL
SELECT '11000002' ID, 'Huairou' PART, '11000000' PID, 'VI' P_CODE, 3 NUM
FROM DUAL
UNION ALL
SELECT '11000002' ID, 'Huairou' PART, '11000000' PID, 'DS' P_CODE, 3 NUM
FROM DUAL
)
SELECT R_ID, R_PART, R_P_CODE, SUM(NUM)
FROM (SELECT CONNECT_BY_ROOT(T.ID) R_ID,
CONNECT_BY_ROOT(T.PART) R_PART,
CONNECT_BY_ROOT(P_CODE) R_P_CODE,
T.NUM
FROM T
START WITH ID = '10000000'
CONNECT BY T.PID = PRIOR ID
AND T.P_CODE = PRIOR P_CODE)
GROUP BY R_ID, R_PART, R_P_CODE;能给全些吗,上半部分是什么意思啊。
上半部分是我构建的临时表,你不用管,这样你可以直接执行并能看到结果。
你可以直接选择以下部分去执行:
SELECT R_ID, R_PART, R_P_CODE, SUM(NUM) FROM (SELECT CONNECT_BY_ROOT(T.ID) R_ID, CONNECT_BY_ROOT(T.PART) R_PART, CONNECT_BY_ROOT(P_CODE) R_P_CODE, T.NUM FROM T START WITH ID = '10000000' CONNECT BY T.PID = PRIOR ID AND T.P_CODE = PRIOR P_CODE) GROUP BY R_ID, R_PART, R_P_CODE; 其中
ID-单位编码
PART-单位名称
PID-上级单位名称
P_CODE-统计类型
NUM-count
你只需要用你的表将以上字段替换掉就行了