데이터베이스 질문과 답변
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레벨 까지 있습니다
by 마농
[2020.01.10 15:51:31]
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
;
-- http://gurubee.net/lecture/2250
by 히준3
[2020.01.10 16:48:16]
감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.