[Oracle 문제] 조인 - 년도별 누적 판매금액이 가장 많은 고객

[Oracle 문제]
조인 - 년도별 누적 판매금액이 가장 많은 고객

---

-- 출력컬럼 : 판매년도, cNum, cName, 년도별판매금액합(qty*bPrice)

-- 판매년도 오름차순 출력

-- book(bCode, bPrice), dsale(sNum, bCode, qty), sale(sNum, sDate, cNum), cus(cNum, cName)

SELECT 판매년도, cName FROM(
    SELECT TO_CHAR(sDate, 'YYYY') 판매년도, c.cNum, cName, SUM(qty*bPrice),
            RANK()OVER(PARTITION BY TO_CHAR(sDate, 'YYYY') ORDER BY SUM(qty*bPrice)DESC) 랭킹
    FROM book b
    JOIN dsale d ON b.bCode = d.bCode
    JOIN sale s ON s.sNum = d.sNum
    JOIN cus c ON s.cNum = c.cNum
    GROUP BY TO_CHAR(sDate, 'YYYY') , c.cNum, cName
) WHERE 랭킹 = 1;

💡서브쿼리를 안쓰면 풀 수 없는 문제다.
랭크 함수를 사용해야 쉽게 풀 수 있다.

 

 

 

년도의 월별 서적 판매 수량 합

---

-- 출력컬럼 : 년도, 책코드, 책이름, M01, M02, ... M12

-- book(bCode, bPrice), dsale(sNum, bCode, qty), sale(sNum, sDate)

SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, b.bName,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '01', qty, 0)),0) MO1,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '02', qty, 0)),0) MO2,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '03', qty, 0)),0) MO3,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '04', qty, 0)),0) MO4,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '05', qty, 0)),0) MO5,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '06', qty, 0)),0) MO6,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '07', qty, 0)),0) MO7,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '08', qty, 0)),0) MO8,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '09', qty, 0)),0) MO9,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '10', qty, 0)),0) M10,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '11', qty, 0)),0) M11,
        NVL( SUM(DECODE(TO_CHAR(sDate, 'MM'), '12', qty, 0)),0) M12           
    FROM book b
    JOIN dsale d ON b.bCode = d.bCode
    JOIN sale s ON s.sNum = d.sNum
    GROUP BY TO_CHAR(sDate, 'YYYY'), b.bCode, b.bName
    ORDER BY 년도, b.bCode;