데이터베이스 질문과 답변
계층 금액 합계 문의 드립니다 ㅜㅜ
쿼리는 아래와 같습니다.
/* Formatted on 2020/02/20 오후 7:06:21 (QP5 v5.256.13226.35510) */
SELECT B.*
,CONNECT_BY_ROOT B.EDU_CRS_CD AS ROOT_NAME
FROM ( SELECT A.EDU_CRS_CD
,MAX (A.EDU_CRS_NM) AS EDU_CRS_NM
,MAX (A.EDU_CRS_UP_CD) AS EDU_CRS_UP_CD
,MAX (A.LEV) AS LEV
,MAX (A.ORD_NO) AS ORD_NO
,MAX (A.JAN_CNT) AS JAN_CNT
,MAX (A.FEB_CNT) AS FEB_CNT
,MAX (A.MAR_CNT) AS MAR_CNT
,MAX (A.APR_CNT) AS APR_CNT
,MAX (A.MAY_CNT) AS MAY_CNT
,MAX (A.JUN_CNT) AS JUN_CNT
,MAX (A.JUL_CNT) AS JUL_CNT
,MAX (A.AUG_CNT) AS AUG_CNT
,MAX (A.SEP_CNT) AS SEP_CNT
,MAX (A.OCT_CNT) AS OCT_CNT
,MAX (A.NOV_CNT) AS NOV_CNT
,MAX (A.DEC_CNT) AS DEC_CNT
, (SELECT SUM (S4.EDU_CNT)
FROM MON_EDU_CNT_MGT S4
WHERE S4.EDU_CRS_CD = A.EDU_CRS_CD
AND S4.YYYY = TO_NUMBER ('2020') - 1)
AS PRE_SUM_EDU_CNT
FROM (SELECT T1.EDU_CRS_CD
,T1.EDU_CRS_NM
,T1.EDU_CRS_UP_CD
,T1.LEV
,T1.ORD_NO
,CASE WHEN T2.MM = '01' THEN EDU_CNT END AS JAN_CNT
,CASE WHEN T2.MM = '02' THEN EDU_CNT END AS FEB_CNT
,CASE WHEN T2.MM = '03' THEN EDU_CNT END AS MAR_CNT
,CASE WHEN T2.MM = '04' THEN EDU_CNT END AS APR_CNT
,CASE WHEN T2.MM = '05' THEN EDU_CNT END AS MAY_CNT
,CASE WHEN T2.MM = '06' THEN EDU_CNT END AS JUN_CNT
,CASE WHEN T2.MM = '07' THEN EDU_CNT END AS JUL_CNT
,CASE WHEN T2.MM = '08' THEN EDU_CNT END AS AUG_CNT
,CASE WHEN T2.MM = '09' THEN EDU_CNT END AS SEP_CNT
,CASE WHEN T2.MM = '10' THEN EDU_CNT END AS OCT_CNT
,CASE WHEN T2.MM = '11' THEN EDU_CNT END AS NOV_CNT
,CASE WHEN T2.MM = '12' THEN EDU_CNT END AS DEC_CNT
FROM EDU_CRS_CD_MGT T1
LEFT OUTER JOIN MON_EDU_CNT_MGT T2
ON T2.EDU_CRS_CD = T1.EDU_CRS_CD
AND T2.YYYY = T1.YYYY
WHERE T1.YYYY = '2020') A
GROUP BY A.EDU_CRS_CD) B
START WITH B.EDU_CRS_UP_CD IS NULL
CONNECT BY PRIOR B.EDU_CRS_CD = B.EDU_CRS_UP_CD
ORDER SIBLINGS BY B.ORD_NO
현재 조회 정보는 첨부 쪽에 이미지로 첨부하였습니다.
상위로 집계 합산 되었으면 합니다 ㅜㅜ 도와주세여
SELECT a.edu_crs_cd
, a.edu_crs_nm
, a.edu_crs_up_cd
, a.lev
, a.ord_no
, b.jan_cnt, b.feb_cnt, b.mar_cnt, b.apr_cnt, b.may_cnt, b.jun_cnt
, b.jul_cnt, b.aug_cnt, b.sep_cnt, b.oct_cnt, b.nov_cnt, b.dec_cnt
, b.pre_sum_edu_cnt
, a.root_name
FROM (SELECT edu_crs_cd
, edu_crs_nm
, edu_crs_up_cd
, lev
, ord_no
, CONNECT_BY_ROOT edu_crs_cd root_name
, ROWNUM rn
FROM edu_crs_cd_mgt
START WITH yyyy = '2020'
AND edu_crs_up_cd IS NULL
CONNECT BY PRIOR yyyy = yyyy
AND PRIOR edu_crs_cd = edu_crs_up_cd
ORDER SIBLINGS BY ord_no
) a
LEFT OUTER JOIN
(SELECT a.edu_crs_root_cd
, SUM(DECODE(b.mm, '01', b.edu_cnt)) jan_cnt
, SUM(DECODE(b.mm, '02', b.edu_cnt)) feb_cnt
, SUM(DECODE(b.mm, '03', b.edu_cnt)) mar_cnt
, SUM(DECODE(b.mm, '04', b.edu_cnt)) apr_cnt
, SUM(DECODE(b.mm, '05', b.edu_cnt)) may_cnt
, SUM(DECODE(b.mm, '06', b.edu_cnt)) jun_cnt
, SUM(DECODE(b.mm, '07', b.edu_cnt)) jul_cnt
, SUM(DECODE(b.mm, '08', b.edu_cnt)) aug_cnt
, SUM(DECODE(b.mm, '09', b.edu_cnt)) sep_cnt
, SUM(DECODE(b.mm, '10', b.edu_cnt)) oct_cnt
, SUM(DECODE(b.mm, '11', b.edu_cnt)) nov_cnt
, SUM(DECODE(b.mm, '12', b.edu_cnt)) dec_cnt
, SUM(DECODE(b.mm, '00', b.edu_cnt)) pre_sum_edu_cnt
FROM (SELECT CONNECT_BY_ROOT edu_crs_cd edu_crs_root_cd
, edu_crs_cd
FROM edu_crs_cd_mgt
START WITH yyyy = '2020'
CONNECT BY PRIOR yyyy = yyyy
AND PRIOR edu_crs_cd = edu_crs_up_cd
) a
INNER JOIN
(SELECT edu_crs_cd
, edu_cnt
, DECODE(yyyy, '2020', mm, '00') mm
FROM mon_edu_cnt_mgt
WHERE yyyy IN ('2020', TO_CHAR('2020'-1))
) b
ON a.edu_crs_cd = b.edu_crs_cd
GROUP BY a.edu_crs_root_cd
) b
ON a.edu_crs_cd = b.edu_crs_root_cd
ORDER BY a.rn
;
-- http://gurubee.net/lecture/2250
감사합니다 ㅜㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.