a와 b 가 같을때 어떤 순서대로 나와도 상관없는거에요?

쪼렙 입니다 : )

커리가 부실하거나 이상한 부분이나 별 필요 없는 부분이 있으면...;;

기찮으시더라도 넓은 아량를 배풀어 리플로 지도 편달 바랍니다. (--)(__)

저의 생각은 입니다.

WITH TEST AS(
SELECT 'A' AS COM , 10000 AS AMT FROM DUAL UNION ALL
SELECT 'B' AS COM , 20000 AS AMT FROM DUAL UNION ALL
SELECT 'C' AS COM , 25000 AS AMT FROM DUAL UNION ALL
SELECT 'D' AS COM , 30000 AS AMT FROM DUAL UNION ALL
SELECT 'E' AS COM , 22000 AS AMT FROM DUAL UNION ALL
SELECT 'F' AS COM , 15000 AS AMT FROM DUAL UNION ALL
SELECT 'A' AS COM , 10000 AS AMT FROM DUAL


)
SELECT
SUM(DECODE(RN , 1, AMT_SUM , 0)) AS A
, SUM(DECODE(RN , 2, AMT_SUM , 0)) AS B
, SUM(DECODE(RN , 3, AMT_SUM , 0)) AS C
, SUM(DECODE(RN , 4, AMT_SUM , 0)) AS D
, SUM(DECODE(RN , 1,0,2,0,3,0,4, 0 ,AMT_SUM)) AS D
FROM
(
SELECT
COM
, AMT
, SUM(AMT) AS AMT_SUM
, RANK() OVER(ORDER BY AMT DESC) AS RN
FROM TEST
GROUP BY
COM
, AMT
)
GROUP BY 1,2,3,4

헉 커리에 오류가 있군요

RANK 로 하니 점수가 같으면 같은 등수로 나오는군요 ;;

ROW_NUMBER 하기에는 순서가 문제가 될꺼 같군요 ;;

같은 점수 이면 순서는 회사 순으로 했습니다. ;;(내공 부족... 더생각해보겠습니
다.)

WITH TEST AS(
SELECT 'A' AS COM , 10000 AS AMT FROM DUAL UNION ALL
SELECT 'B' AS COM , 20000 AS AMT FROM DUAL UNION ALL
SELECT 'C' AS COM , 25000 AS AMT FROM DUAL UNION ALL
SELECT 'D' AS COM , 30000 AS AMT FROM DUAL UNION ALL
SELECT 'E' AS COM , 22000 AS AMT FROM DUAL UNION ALL
SELECT 'F' AS COM , 15000 AS AMT FROM DUAL UNION ALL
SELECT 'A' AS COM , 10000 AS AMT FROM DUAL


)
SELECT
SUM(DECODE(RN , 1, AMT_SUM , 0)) AS A
, SUM(DECODE(RN , 2, AMT_SUM , 0)) AS B
, SUM(DECODE(RN , 3, AMT_SUM , 0)) AS C
, SUM(DECODE(RN , 4, AMT_SUM , 0)) AS D
, SUM(DECODE(RN , 1,0,2,0,3,0,4, 0 ,AMT_SUM)) AS D
FROM
(
SELECT
COM
, AMT
, SUM(AMT) AS AMT_SUM
, ROW_NUMBER() OVER(ORDER BY AMT DESC) AS RN
FROM TEST
GROUP BY
COM
, AMT
)
GROUP BY 1,2,3,4

SELECT LEAST(rn, 5) rk
, CASE WHEN rn <= 4 THEN com ELSE '이외' END com
, SUM(amt) amt
FROM
(
SELECT com
, SUM(amt) amt
, ROW_NUMBER() OVER(ORDER BY SUM(amt) DESC) rn
FROM test
GROUP BY com
)
GROUP BY LEAST(rn, 5)
, CASE WHEN rn <= 4 THEN com ELSE '이외' END
ORDER BY 1
;

SELECT
MIN(DECODE(RN , 1, COM )) AS A_COM
, MIN(DECODE(RN , 1, AMT_SUM )) AS A
, MIN(DECODE(RN , 2, COM )) AS B_COM
, MIN(DECODE(RN , 2, AMT_SUM )) AS B
, MIN(DECODE(RN , 3, COM )) AS C_COM
, MIN(DECODE(RN , 3, AMT_SUM )) AS C
, MIN(DECODE(RN , 4, COM )) AS D_COM
, MIN(DECODE(RN , 4, AMT_SUM )) AS D
, SUM(DECODE(SIGN(4-RN),-1,AMT_SUM)) ETC
FROM (
SELECT
COM
, AMT
, SUM(AMT) AS AMT_SUM
, ROW_NUMBER() OVER(ORDER BY AMT DESC) AS RN
FROM TEST
GROUP BY
COM
, AMT
)

SELECT MIN(DECODE(rn,1,com)) com_1
, MIN(DECODE(rn,1,amt)) amt_1
, MIN(DECODE(rn,2,com)) com_2
, MIN(DECODE(rn,2,amt)) amt_2
, MIN(DECODE(rn,3,com)) com_3
, MIN(DECODE(rn,3,amt)) amt_3
, MIN(DECODE(rn,4,com)) com_4
, MIN(DECODE(rn,4,amt)) amt_4
, '이외' com_etc
, SUM(CASE WHEN rn > 4 THEN amt END) amt_etc
FROM
(
SELECT com
, SUM(amt) amt
, ROW_NUMBER() OVER(ORDER BY SUM(amt) DESC) rn
FROM test
GROUP BY com
)
;

헉...

컬럼명두 보여주는거군요 ;;

SELECT
MAX(DECODE(RN , 1, COM)) AS COM_1
, MAX(DECODE(RN , 1, AMT_SUM , 0)) AS A
, MAX(DECODE(RN , 2, COM)) AS COM_2
, MAX(DECODE(RN , 2, AMT_SUM , 0)) AS B
, MAX(DECODE(RN , 3, COM)) AS COM_3
, MAX(DECODE(RN , 3, AMT_SUM , 0)) AS C
, MAX(DECODE(RN , 4, COM)) AS COM_4
, MAX(DECODE(RN , 4, AMT_SUM , 0)) AS D
, MAX(DECODE(RN , 1,0,2,0,3,0,4, 0 ,AMT_SUM)) AS D
FROM
(
SELECT
COM
, AMT
, SUM(AMT) AS AMT_SUM
, ROW_NUMBER() OVER(ORDER BY AMT DESC) AS RN
FROM TEST
GROUP BY
COM
, AMT
)
GROUP BY 1,2,3,4