데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

실력이 변변치 못해 좀 긴데,이해 바랍니다.

마농님을 비롯한 다른 실력 좋으신 분들의 조언을 들어보세요.

RANK부분에 오류가 있어 수정합니다.

11G부터는 PIVOT을 지원한다고 하니 사용하면 라인수가 줄어 들겠네요.

WITH

A(NO,COUNT,LOWER_BND)

(

SELECT 'A001',1,80 FROM DUAL

B(NO,COUNT,I_NO,AMT)

(

SELECT 'A001',1,1,1000 FROM DUAL UNION ALL

SELECT 'A001',1,2,3000 FROM DUAL

C(NO ,COUNT,SEQ,VENDOR,AMT)

(

SELECT 'A001',1,1,'A1', 3000 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A2',1000 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A3',4000 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A4',3500 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A5',4200 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A6',4200 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A7',4200 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A8',4200 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A9',4200 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A10',4200 FROM DUAL UNION ALL

SELECT 'A001',1,1,'A11',4200 FROM DUAL UNION ALL

SELECT 'A001',1,2,'A1', 3201 FROM DUAL UNION ALL

SELECT 'A001',1,2,'A2',1200 FROM DUAL UNION ALL

SELECT 'A001',1,2,'A3',3400 FROM DUAL

)

SELECT

F.RN,F.NO,

MIN(DECODE(F.SEQ,1,F.SEQ))SEQ1,

MIN(DECODE(F.SEQ,1,F.RANK))RANK1,

MIN(DECODE(F.SEQ,1,F.VENDOR))VENDOR1,

MIN(DECODE(F.SEQ,1,F.AMT))AMT1,

MIN(DECODE(F.SEQ,2,F.SEQ))SEQ2,

MIN(DECODE(F.SEQ,2,F.RANK))RANK2,

MIN(DECODE(F.SEQ,2,F.VENDOR))VENDOR2,

MIN(DECODE(F.SEQ,2,F.AMT))AMT2,

MIN(DECODE(F.SEQ,3,F.SEQ))SEQ3,

MIN(DECODE(F.SEQ,3,F.RANK))RANK3,

MIN(DECODE(F.SEQ,3,F.VENDOR))VENDOR3,

MIN(DECODE(F.SEQ,3,F.AMT))AMT3,

MIN(DECODE(F.SEQ,4,F.SEQ))SEQ4,

MIN(DECODE(F.SEQ,4,F.RANK))RANK4,

MIN(DECODE(F.SEQ,4,F.VENDOR))VENDOR4,

MIN(DECODE(F.SEQ,4,F.AMT))AMT4,

MIN(DECODE(F.SEQ,5,F.SEQ))SEQ5,

MIN(DECODE(F.SEQ,5,F.RANK))RANK5,

MIN(DECODE(F.SEQ,5,F.VENDOR))VENDOR5,

MIN(DECODE(F.SEQ,5,F.AMT))AMT5,

MIN(DECODE(F.SEQ,6,F.SEQ))SEQ6,

MIN(DECODE(F.SEQ,6,F.RANK))RANK6,

MIN(DECODE(F.SEQ,6,F.VENDOR))VENDOR6,

MIN(DECODE(F.SEQ,6,F.AMT))AMT6,

MIN(DECODE(F.SEQ,7,F.SEQ))SEQ7,

MIN(DECODE(F.SEQ,7,F.RANK))RANK7,

MIN(DECODE(F.SEQ,7,F.VENDOR))VENDOR7,

MIN(DECODE(F.SEQ,7,F.AMT))AMT7,

MIN(DECODE(F.SEQ,8,F.SEQ))SEQ8,

MIN(DECODE(F.SEQ,8,F.RANK))RANK8,

MIN(DECODE(F.SEQ,8,F.VENDOR))VENDOR8,

MIN(DECODE(F.SEQ,8,F.AMT))AMT8,

MIN(DECODE(F.SEQ,9,F.SEQ))SEQ9,

MIN(DECODE(F.SEQ,9,F.RANK))RANK9,

MIN(DECODE(F.SEQ,9,F.VENDOR))VENDOR9,

MIN(DECODE(F.SEQ,9,F.AMT))AMT9,

MIN(DECODE(F.SEQ,10,F.SEQ))SEQ10,

MIN(DECODE(F.SEQ,10,F.RANK))RANK10,

MIN(DECODE(F.SEQ,10,F.VENDOR))VENDOR10,

MIN(DECODE(F.SEQ,10,F.AMT))AMT10

FROM

(

SELECT

C.NO,C.COUNT,C.SEQ,C.VENDOR,C.AMT,E.LOWER_BND,

CASE WHEN C.AMT <= E.LOWER_BND THEN RANK() OVER(PARTITION BY C.NO,C.COUNT,C.SEQ ORDER BY C.NO,C.COUNT,C.SEQ,C.AMT)||''

ELSE '탈락' END RANK,

ROW_NUMBER() OVER(PARTITION BY C.NO,C.COUNT,C.SEQ ORDER BY C.NO,C.COUNT,C.SEQ,C.AMT) RN

FROM

(

SELECT

D.NO,D.COUNT,(D.AMT*A.LOWER_BND*0.01) LOWER_BND

FROM (SELECT B.NO,B.COUNT,SUM(B.AMT) AMT FROM B GROUP BY B.NO,B.COUNT) D,A

WHERE D.NO=A.NO AND D.COUNT=A.COUNT

) E,C

WHERE

E.NO=C.NO AND E.COUNT=C.COUNT

) F

GROUP BY F.NO,F.RN ORDER BY F.NO,F.RN

빈들의아빠(hyperrookie)님이 2015-10-03 14:34에 작성한 댓글입니다.
이 댓글은 2015-10-04 17:14에 마지막으로 수정되었습니다.