Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 |
Tags
- vaadin
- plugin
- Express
- mapreduce
- Spring
- 보조정렬
- SQL
- IntelliJ
- Eclipse
- SSL
- SPC
- window
- Android
- mybatis
- JavaScript
- R
- GIT
- Python
- es6
- tomcat
- hadoop
- NPM
- react
- MSSQL
- 공정능력
- Java
- table
- xPlatform
- Sqoop
- Kotlin
Archives
- Today
- Total
DBILITY
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
반응형
'database > sql' 카테고리의 다른 글
연산결과인 그룹핑 컬럼의 분모가 0이 되는 경우 data conversion error 대처방안 (0) | 2017.07.19 |
---|---|
데이터 분포에 따라 번호 배정하기 (0) | 2017.06.28 |
oracle unpivot column to row (열을 행으로) 변환하기 (0) | 2016.10.12 |
oracle 점이력 선분이력으로 만들기 (0) | 2016.10.10 |
현재시간 1초 전 구하기 (0) | 2016.10.10 |
Comments