[DB] SQL 문제

문제

2020년도 상품별 매입금액과 매출 금액을 구하기

ANSI, Oracle방식, 서브쿼리

CART_NO가 인덱스이기 때문에
SUBSTR보다 LIKE가 더 빠르다

ANSI

SELECT B.PROD_ID, SUM(B.PROD_SALE*A.CART_QTY), SUM(B.PROD_COST*C.BUY_QTY)
FROM PROD B
LEFT OUTER JOIN CART A ON A.CART_PROD = B.PROD_ID 
    AND A.CART_NO LIKE '2020%'
LEFT OUTER JOIN BUYPROD C ON B.PROD_ID=C.BUY_PROD AND EXTRACT(YEAR FROM BUY_DATE)='2020'
GROUP BY B.PROD_ID;

순서 : ON조건에 해당하는 데이터를 필터링(2020년인 CART, BUYPROD 데이터 외엔 유실) > OUTER JOIN 

틀린 예제

SELECT B.PROD_ID, SUM(B.PROD_SALE*A.CART_QTY), SUM(B.PROD_COST*C.BUY_QTY)
FROM PROD B
LEFT OUTER JOIN CART A ON A.CART_PROD = B.PROD_ID 
LEFT OUTER JOIN BUYPROD C ON B.PROD_ID=C.BUY_PROD AND 
WHERE A.CART_NO LIKE '2020%'
AND EXTRACT(YEAR FROM BUY_DATE)='2020'
GROUP BY B.PROD_ID;

WHERE 조건은 PROD가 2020년에 판매된게 없으면 데이터가 유실될 수도 있다

만약 cart나 buyprod에 2020년에 판매된 prod가 없을 경우 해당 prod

OUTER JOIN > (WHERE 조건에 2020년에 팔린 상품이 없는 경우에 PROD,CART,BUYPROD자체의 데이터도 유실)  

Oracle

SELECT B.PROD_ID, SUM(B.PROD_SALE*A.CART_QTY), SUM(B.PROD_COST*C.BUY_QTY)
FROM PROD B, CART A, BUYPROD C
WHERE A.CART_NO(+) LIKE '2020%'
AND EXTRACT(YEAR FROM BUY_DATE(+)) = '2020'
AND A.CART_PROD(+) = B.PROD_ID
AND B.PROD_ID=C.BUY_PROD(+)
GROUP BY B.PROD_ID;

서브쿼리

SELECT B.PROD_ID,
(SELECT SUM(B.PROD_SALE*A.CART_QTY)
FROM CART A
WHERE A.CART_NO LIKE '2020%')"매출 금액",
(SELECT SUM(B.PROD_COST*C.BUY_QTY)
FROM BUYPROD C
WHERE EXTRACT(YEAR FROM BUY_DATE)='2020') "매입 금액"
FROM PROD B;

SELECT A.PROD_ID, 
 B.CSUM "매출 금액",
 C.PSUM "매입 금액"
FROM PROD A,
(SELECT B.PROD_ID PID, SUM(B.PROD_SALE*A.CART_QTY) AS CSUM
FROM PROD B, CART A
WHERE A.CART_NO LIKE '2020%'
GROUP BY B.PROD_ID) B,
(SELECT B.PROD_ID PID, SUM(B.PROD_COST*C.BUY_QTY) AS PSUM
FROM PROD B, BUYPROD C
WHERE EXTRACT(YEAR FROM BUY_DATE)='2020'
GROUP BY B.PROD_ID)C
WHERE B.PID(+)=A.PROD_ID
AND C.PID(+)=A.PROD_ID