데이터베이스 질문과 답변
WITH T AS(
SELECT 'A' LI, 'AB' ID, 1 CNT1, 0 CNT2, 1 DCNT1, 0 DCNT2, 'DR1' CNAME, '3.424242' TTIME, 'AB' RID, NULL PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ABA' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR2' CNAME, '1.424242' TTIME, 'AB' RID, 'AB' PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ABB' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR3' CNAME, '2.424242' TTIME, 'AB' RID, 'AB' PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ABC' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR4' CNAME, '6.424242' TTIME, 'AB' RID, 'AB' PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'AC' ID, 1 CNT1, 0 CNT2, 1 DCNT1, 0 DCNT2, 'DR1' CNAME, '3.424242' TTIME, 'AC' RID, NULL PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ACA' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR2' CNAME, '1.424242' TTIME, 'AC' RID, 'AC' PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ACB' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR3' CNAME, '2.424242' TTIME, 'AC' RID, 'AC' PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ACC' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR4' CNAME, '6.424242' TTIME, 'AC' RID, 'AC' PID FROM DUAL
)
SELECT * FROM T
원하는 결과는요...
A | AB | 4 | 4 | 0 | 3.424242
A | AC | 4 | 4 | 0 | 3.424242
제일 첫번째 줄이 부모 데이타 입니다 PID가 NULL인 것입니다
나머진 자식 데이타 입니다
부모 데이타 기준으로
1) A값은 GROUP BY 할것이고
2) AB값은 부모값을 가져옵니다
3) 4 값은 부모 CNT1 (1) + 자식 CNT2(3)을 더한 1+3 이라는 값을 가져 올 것입니다
4) 4 값은 DCNT1은 총 합계이며
5) 0 값은 DCNT2의 총 합계입니다
6) 3.424242 값은 부모의 TTIME을 가져옵니다
쿼리 짜기가 참 애매 하네요;;;
도와 주세요ㅠㅠ
내용 약간 수정 했습니다
WITH T AS(
SELECT 'A' LI, 'AB' ID, 1 CNT1, 0 CNT2, 1 DCNT1, 0 DCNT2, 'DR1' CNAME, '3.424242' TTIME, 'AB' RID, NULL PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ABA' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR2' CNAME, '1.424242' TTIME, 'AB' RID, 'AB' PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ABB' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR3' CNAME, '2.424242' TTIME, 'AB' RID, 'AB' PID FROM DUAL
UNION ALL
SELECT 'A' LI, 'ABC' ID, 0 CNT1, 3 CNT2, 1 DCNT1, 0 DCNT2, 'DR4' CNAME, '6.424242' TTIME, 'AB' RID, 'AB' PID FROM DUAL
)
SELECT LI,
MAX(CASE WHEN PID IS NULL THEN ID END) AS ID,
MAX(CNT1) + MAX(CNT2) AS CNT,
SUM(DCNT1) AS DCNT1,
SUM(DCNT2) AS DCNT2,
MAX(CASE WHEN PID IS NULL THEN TTIME END) AS TTIME
FROM T
GROUP BY LI
select
li,
max(case when tlvl = 1 then id end) as col2,
count(*) as col3,
sum(dcnt1) as col4,
sum(dcnt2) as col5,
max(case when tlvl = 1 then ttime end) as col6
from
(
select
t.*,
level as tlvl
from
t
start with t.pid is null
connect by prior t.id = t.pid and t.li = prior t.li
) t1
group by li
글 내용 약간 수정 했습니다
제가 글 설명을 제대로 못해드렸나봐요
원하는 결과가 안나오네요
GROUP BY LI, ID해도 안나오네요;;;ㅠㅠ
select li
, max(id) id
, sum(distinct cnt) cnt
, sum(dcnt1) dnct1
, sum(dcnt2) dcnt2
, max(decode(root, 0, ttime)) ttime
from ( select li
, connect_by_root id AS id
, cnt1 + cnt2 cnt
, dcnt1
, dcnt2
, ttime
, connect_by_isleaf root
from t
start with pid is null
connect by prior id = pid
)
group by li, id;
제로님 감사합니다
덕분에 잘 됩니다^^
) 버튼을 클릭하여 작성 하시면 됩니다.