데이터베이스 질문과 답변
With Test As (
Select '001' no, '20120401' exe_Date, '' a, '80' b, '40' c, '74' d From Dual Union All
Select '001' no, '20120405' exe_Date, '61' a, '90' b, '50' c, '' d From Dual Union All
Select '002' no, '20120401' exe_Date, '90' a, '' b, '50' c, '' d From Dual Union All
Select '002' no, '20120411' exe_Date, '85' a, '70' b, '60' c, '' d From Dual
)
Select * From Test;
원본 데이터) 일자별 취득학점
NO EXE_DATE A B C D
-------------------------------------
001 20120401 80 40 74
001 20120405 61 90 50
002 20120401 90 50
002 20120411 85 70 60
원하는 결과) 개인별 최초 취득학점
NO A B C D
-------------------------
001 61 80 40 74
002 90 70 50
by 마농
[2012.04.23 17:22:58]
WITH Test AS
(
SELECT '001' no, '20120401' exe_Date, null a, 80 b, 40 c, 74 d FROM dual
UNION ALL SELECT '001', '20120405', 61, 90, 50, null FROM dual
UNION ALL SELECT '002', '20120401', 90, null, 50, null FROM dual
UNION ALL SELECT '002', '20120411', 85, 70, 60, null FROM dual
)
SELECT No
, MAX(a) KEEP(DENSE_RANK FIRST ORDER BY NVL2(a, 1, 2), exe_Date) a
, MAX(b) KEEP(DENSE_RANK FIRST ORDER BY NVL2(b, 1, 2), exe_Date) b
, MAX(c) KEEP(DENSE_RANK FIRST ORDER BY NVL2(c, 1, 2), exe_Date) c
, MAX(d) KEEP(DENSE_RANK FIRST ORDER BY NVL2(d, 1, 2), exe_Date) d
FROM Test
GROUP BY No
;
by 손님
[2012.04.24 11:20:04]
뜨~악! 마농님 감사합니다. 멋집니다요 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.