SELECT CODE
,UP_CODE
,CODE_NAME
,UNIT
,QTY
,UNIT_COST
,COST <-- SUM할 값
,(SELECT SUM(COST)
FROM CBS
START WITH CODE = MCBS.CODE
CONNECT BY PRIOR CODE = UP_CODE
) AS COST_SUM
FROM CBS MCBS
START WITH UP_CODE IS NULL
CONNECT BY PRIOR CODE = UP_CODE
ORDER SIBLINGS BY CODE;
계층쿼리에서 하위에 대한 합계 값을 구할시 스칼라 말고는 방법이 없나요?
7레벨 까지 있습니다
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SELECT m.code , m.up_code , m.code_name , m.unit , m.qty , m.unit_cost , m.cost , s.cost_sum FROM cbs m , ( SELECT code , SUM (cost) cost_sum FROM ( SELECT CONNECT BY ROOT code code , cost FROM cbs CONNECT BY PRIOR code = up_code ) GROUP BY code ) s WHERE m.code = s.code START WITH m.up_code IS NULL CONNECT BY PRIOR m.code = m.up_code ORDER SIBLINGS BY m.code ; |