oracle 입찰시 기준율을 적용 업체별 순위 표시

전년도(2015)에 database.sarang.net에 답변해 드린 내용입니다.

지금보니 내가 한게 맞는지 의문이 들고 있음.

11G부터는 PIVOT을 지원합니다.

WITH
A(NO,COUNT,LOWER_BND)
AS
(
	SELECT 'A001',1,80 FROM DUAL
),
B(NO,COUNT,I_NO,AMT)
AS
(
	SELECT 'A001',1,1,1000 FROM DUAL UNION ALL
	SELECT 'A001',1,2,3000 FROM DUAL
),
C(NO	,COUNT,SEQ,VENDOR,AMT)
AS
(
	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