데이터베이스 질문과 답변
by 스마트 정 [2016.06.07 09:23:05]
* 법인카드의 월별 지급한도금액을 뽑아내려고 하는데, 한도금액이 변경되기 전에는 이전 한도금액을 표기해야 합니다.
WITH TEST_TABLE AS (
SELECT '1234-1234-1234-1234' card_no,'2016-01' yymm, 500000 limamt FROM DUAL UNION ALL
SELECT '1234-1234-1234-1234' card_no,'2016-06' yymm, 700000 limamt FROM DUAL
)
SELECT card_no, yymm, limamt
FROM TEST_TABLE
==================================================================
월하는 결과 ]
card_no yymm limamt
--------------------------------------------------------------
1234-1234-1234-1234 2016-01 500000
1234-1234-1234-1234 2016-02 500000
1234-1234-1234-1234 2016-03 500000
1234-1234-1234-1234 2016-04 500000
1234-1234-1234-1234 2016-05 500000
1234-1234-1234-1234 2016-06 700000
WITH TEST_TABLE AS (
SELECT '1234-1234-1234-1234' card_no,'2016-01' yymm, 500000 limamt FROM DUAL UNION ALL
SELECT '1234-1234-1234-1234' card_no,'2016-06' yymm, 700000 limamt FROM DUAL
), MM AS (
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2016-01','YYYY-MM'), LEVEL -1 ),'YYYY-MM') YYMM FROM DUAL CONNECT BY LEVEL <= 6
)
SELECT YYMM
, FIRST_VALUE(CARD_NO) OVER(PARTITION BY GB ORDER BY YYMM) CARD_NO
, FIRST_VALUE(LIMAMT) OVER(PARTITION BY GB ORDER BY YYMM) LIMAMT
FROM (
SELECT SUM(DECODE(CARD_NO,NULL,0,1)) OVER(ORDER BY T.YYMM) GB
, T.*
FROM (SELECT ROW_NUMBER() OVER(ORDER BY B.YYMM) RN
, A.CARD_NO , A.LIMAMT
, B.YYMM
FROM TEST_TABLE A , MM B
WHERE A.YYMM(+) = B.YYMM
ORDER BY B.YYMM
) T
)
너무 멋찌십니다.
감사합니다.
WITH TEST_TABLE AS (
SELECT '1234-1234-1234-1234' card_no,'2016-01' yymm, 500000 limamt FROM DUAL UNION ALL
SELECT '1234-1234-1234-1234' card_no,'2016-06' yymm, 700000 limamt FROM DUAL
),
T AS (
SELECT SUBSTR('201601' + LEVEL - 1, 1, 4) || '-' || SUBSTR('201601' + LEVEL - 1, 5, 2) yymm
FROM DUAL
CONNECT BY LEVEL <= '201606' - '201601' + 1
)
SELECT LAST_VALUE(TT.card_no ignore nulls) OVER(ORDER BY T.yymm) card_no,
T.yymm,
LAST_VALUE(TT.limamt ignore nulls) OVER(ORDER BY T.yymm) limamt
FROM TEST_TABLE TT, T
WHERE TT.yymm(+) = T.yymm
제가 어렵게 짰네요 ㅡㅡ;
저도 어짜피 카피쟁이라 하하..
오히려 많이 배우고 있습니다 : )
감사합니다.
WITH TEST_TABLE AS ( SELECT '1234-1234-1234-1234' CARD_NO,'2016-01' YYMM, 500000 LIMAMT FROM DUAL UNION ALL SELECT '5678-5678-5678-5678' CARD_NO,'2016-04' YYMM, 600000 LIMAMT FROM DUAL UNION ALL SELECT '1234-1234-1234-1234' CARD_NO,'2016-06' YYMM, 700000 LIMAMT FROM DUAL ), T (CARD_NO, YYMM, LIMAMT) AS ( SELECT CARD_NO, YYMM, LIMAMT FROM ( SELECT CARD_NO, YYMM, LIMAMT , ROW_NUMBER() OVER(PARTITION BY CARD_NO ORDER BY YYMM ) RN FROM TEST_TABLE ) WHERE RN = 1 UNION ALL SELECT A.CARD_NO , TO_CHAR(ADD_MONTHS(TO_DATE(A.YYMM||'-01','yyyy-mm-dd'),1),'yyyy-mm') , NVL(B.LIMAMT,A.LIMAMT) LIMAMT FROM T A ,TEST_TABLE B WHERE A.CARD_NO = B.CARD_NO(+) AND TO_CHAR(ADD_MONTHS(TO_DATE(A.YYMM||'-01','yyyy-mm-dd'),1),'yyyy-mm') = B.YYMM(+) AND A.YYMM < (SELECT MAX(YYMM) FROM TEST_TABLE) ) SELECT * FROM T ORDER BY YYMM , CARD_NO
재귀 쿼리로 짜보았습니다.
감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.