사용자의 하나카드 거래 이력 테이블을 조회하는 쿼리다.
WHERE절 서브쿼리를 활용해서 주민등록번호로 보유한 카드번호를 가져온 뒤 거래이력을 조회하게 된다.
SELECT절 서브쿼리(스칼라 서브쿼리)를 활용해서 결제방식, 결제수단을 코드테이블과 매핑하여 코드값으로 변환했다.
SELECT T.CARD_TRANSACTION_TIME, T.CARD_TRANSACTION_CONTENT, T.CARD_TRANSACTION_AMOUNT,
(SELECT VALUE FROM CODE WHERE CODE = T.PAYMENT) AS PAYMENT, (SELECT L.CARD_PRODUCT_NAME
FROM CARD_PRODUCT_LIST_TEST01 L
WHERE L.CARD_PRODUCT_CODE = (SELECT C.CARD_PRODUCT_CODE
FROM CARD_CONTRACT_TEST01 C
WHERE C.CARD_NUM = T.CARD_NUM)
) AS MEANS
FROM CARD_TRANSACTION_TEST01 T
WHERE CARD_NUM IN(SELECT CARD_NUM FROM CARD_CONTRACT_TEST01 WHERE RESI_NUM = '9203101937988')
ORDER BY CARD_TRANSACTION_TIME;
그리고 이렇게 처리한 Result Set으로 현재년 월별 사용 합계를 GROUP BY 하는 쿼리다.
WITH을 사용하여 반복되는 FROM 절의 코드를 줄일 수 있다.
- WITH으로 선언한 SELECT 문장이 (AS 는 WHOLE) 아래의 서브쿼리절 내에 FROM 절에 쓰였다.
아주 복잡한 구조다.
WITH WHOLE AS (SELECT T.CARD_TRANSACTION_TIME, T.CARD_TRANSACTION_CONTENT, T.CARD_TRANSACTION_AMOUNT,
(SELECT VALUE FROM CODE WHERE CODE = T.PAYMENT) AS PAYMENT, (SELECT L.CARD_PRODUCT_NAME
FROM CARD_PRODUCT_LIST_TEST01 L
WHERE L.CARD_PRODUCT_CODE = (SELECT C.CARD_PRODUCT_CODE
FROM CARD_CONTRACT_TEST01 C
WHERE C.CARD_NUM = T.CARD_NUM)
) AS MEANS
FROM CARD_TRANSACTION_TEST01 T
WHERE CARD_NUM IN(SELECT CARD_NUM FROM CARD_CONTRACT_TEST01 WHERE RESI_NUM = '9203101937988')
ORDER BY CARD_TRANSACTION_TIME)
SELECT SUBQUERY1.NUM AS "1월", SUBQUERY2.NUM AS "2월", SUBQUERY3.NUM AS "3월", SUBQUERY4.NUM AS "4월",
SUBQUERY5.NUM AS "5월", SUBQUERY6.NUM AS "6월", SUBQUERY7.NUM AS "7월", SUBQUERY8.NUM AS "8월",
SUBQUERY9.NUM AS "9월", SUBQUERY10.NUM AS "10월", SUBQUERY11.NUM AS "11월", SUBQUERY12.NUM AS "12월"
FROM (SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-01'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY1,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-02'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY2,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-03'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY3,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-04'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY4,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-05'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY5,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-06'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY6,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-07'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY7,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-08'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY8,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-09'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY9,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-10'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY10,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-11'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY11,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-12'
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY12
WHERE ROWNUM <=1;
SELECT T.CARD_TRANSACTION_TIME, T.CARD_TRANSACTION_CONTENT, T.CARD_TRANSACTION_AMOUNT,
(SELECT VALUE FROM CODE WHERE CODE = T.PAYMENT) AS PAYMENT, (SELECT L.CARD_PRODUCT_NAME
FROM CARD_PRODUCT_LIST_TEST01 L
WHERE L.CARD_PRODUCT_CODE = (SELECT C.CARD_PRODUCT_CODE
FROM CARD_CONTRACT_TEST01 C
WHERE C.CARD_NUM = T.CARD_NUM)
) AS MEANS
FROM CARD_TRANSACTION_TEST01 T
WHERE CARD_NUM IN(SELECT CARD_NUM FROM CARD_CONTRACT_TEST01 WHERE RESI_NUM = '9203101937988')
ORDER BY CARD_TRANSACTION_TIME;오픈뱅킹 거래내역을 UNION ALL 해서 월별 합계를 구하는 쿼리
WITH WHOLE AS (SELECT T.CARD_TRANSACTION_TIME, T.CARD_TRANSACTION_CONTENT, T.CARD_TRANSACTION_AMOUNT, T.PAYMENT, (SELECT L.CARD_PRODUCT_NAME
FROM CARD_PRODUCT_LIST_TEST01 L
WHERE L.CARD_PRODUCT_CODE = (SELECT C.CARD_PRODUCT_CODE
FROM CARD_CONTRACT_TEST01 C
WHERE C.CARD_NUM = T.CARD_NUM)
) AS MEANS
FROM CARD_TRANSACTION_TEST01 T WHERE CARD_NUM IN (SELECT CARD_NUM FROM CARD_CONTRACT_TEST01 WHERE RESI_NUM = #{resiNum})
UNION ALL
SELECT CARD_TRANSACTION_TIME, CARD_TRANSACTION_CONTENT, CARD_TRANSACTION_AMOUNT, PAYMENT, MEANS
FROM OPEN_CARD_TRANSACTION_TEST01
WHERE RESI_NUM = #{resiNum} AND MEANS IN (#{cardListString})
ORDER BY CARD_TRANSACTION_TIME)WITH WHOLE AS (SELECT T.CARD_TRANSACTION_TIME, T.CARD_TRANSACTION_CONTENT, T.CARD_TRANSACTION_AMOUNT, T.PAYMENT, (SELECT L.CARD_PRODUCT_NAME
FROM CARD_PRODUCT_LIST_TEST01 L
WHERE L.CARD_PRODUCT_CODE = (SELECT C.CARD_PRODUCT_CODE
FROM CARD_CONTRACT_TEST01 C
WHERE C.CARD_NUM = T.CARD_NUM)
) AS MEANS
FROM CARD_TRANSACTION_TEST01 T WHERE CARD_NUM IN (SELECT CARD_NUM FROM CARD_CONTRACT_TEST01 WHERE RESI_NUM = #{resiNum})
UNION ALL
SELECT CARD_TRANSACTION_TIME, CARD_TRANSACTION_CONTENT, CARD_TRANSACTION_AMOUNT, PAYMENT, MEANS
FROM OPEN_CARD_TRANSACTION_TEST01
WHERE RESI_NUM = #{resiNum} AND MEANS IN (#{cardListString})
ORDER BY CARD_TRANSACTION_TIME)
SELECT SUBQUERY1.NUM AS "jan", SUBQUERY2.NUM AS "feb", SUBQUERY3.NUM AS "mar", SUBQUERY4.NUM AS "apr",
SUBQUERY5.NUM AS "may", SUBQUERY6.NUM AS "jun", SUBQUERY7.NUM AS "jul", SUBQUERY8.NUM AS "aug",
SUBQUERY9.NUM AS "sept", SUBQUERY10.NUM AS "oct", SUBQUERY11.NUM AS "nov", SUBQUERY12.NUM AS "dec"
FROM (SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-01' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY1,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-02' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY2,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-03' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY3,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-04' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY4,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-05' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY5,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-06' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY6,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-07' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY7,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-08' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY8,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-09' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY9,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-10' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY10,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-11' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY11,
(SELECT *
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
FROM WHOLE
GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-12' FROM DUAL)
UNION ALL
-- SELECT NULL AS NUM
SELECT 0 AS NUM
FROM DUAL)
WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY12
WHERE ROWNUM = 1
<!-- 세미콜론 쓰지 않는 것에 주의! 또한 ROWNUM <=1 하니까 빨간 밑줄 뜬다. 에러는 안나겠지만, 그냥 등호(=)로 바꿔줌. -->'데이터베이스 > ORACLE' 카테고리의 다른 글
| [Oracle] 한글 Alias에는 반드시 더블쿼테이션("")을 붙여야 함 (0) | 2020.09.05 |
|---|---|
| [Oracle] 20-05-06 수업 (0) | 2020.09.04 |
| [Oracle] 연산이 필요한 게 아니라면 NUMBER 타입을 쓰지 말자 (0) | 2020.09.02 |
| [Oracle] 비교절에 현재시간 + 문자열 넣기 (0) | 2020.08.31 |
| [Oracle] 조회된 ROW가 없을 때, RETURN 하는 방법 (0) | 2020.08.31 |
