|
ORG_ID_NO |
CE_SEQ_NO |
FILTER_CODE |
FILTER_CHK |
FILTER_VAL |
|
1018116293 |
15 |
FT01 |
N |
0 |
|
1018116293 |
15 |
FT02 |
Y |
30.21 |
|
1018116293 |
15 |
FT06 |
N |
0 |
|
1018116293 |
15 |
FT10 |
N |
0 |
|
1018116293 |
15 |
FT11 |
N |
0 |
|
1018116293 |
15 |
FT15 |
N |
0 |
|
1018116293 |
15 |
FT16 |
N |
0 |
|
1058156337 |
17 |
FT01 |
N |
0 |
|
1058156337 |
17 |
FT02 |
Y |
14.96 |
|
1058156337 |
17 |
FT06 |
N |
0 |
|
1058156337 |
17 |
FT09 |
Y |
20.04 |
|
1058156337 |
17 |
FT10 |
Y |
13.44 |
|
1058156337 |
17 |
FT11 |
N |
0 |
이미지가 작으니 클릭해서 크게해서 보세요~
답까지 적어놓으면
1. MS-SQL로 했을 경우
SELECT ORG_ID_NO, CE_SEQ_NO, max(fc1) fc1, max(fcc1) fcc1, max(fc2) fc2, max(fcc2) fcc2, max(fc3) fc3, max(fcc3) fcc3,
max(fc4) fc4, max(fcc4) fcc4, max(fc5) fc5, max(fcc5) fcc5, max(fc6) fc6, max(fcc6) fcc6,
max(fc7) fc7, max(fcc7) fcc7
FROM
(
SELECT ORG_ID_NO, CE_SEQ_NO,
case RANK when 1 then filter_code else '-' end as fc1, case RANK when 1 then filter_chk else '-' end as fcc1 ,
case RANK when 2 then filter_code else '-' end as fc2, case RANK when 2 then filter_chk else '-' end as fcc2,
case RANK when 3 then filter_code else '-' end as fc3, case RANK when 3 then filter_chk else '-' end as fcc3 ,
case RANK when 4 then filter_code else '-' end as fc4, case RANK when 4 then filter_chk else '-' end as fcc4 ,
case RANK when 5 then filter_code else '-' end as fc5, case RANK when 5 then filter_chk else '-' end as fcc5,
case RANK when 6 then filter_code else '-' end as fc6, case RANK when 6 then filter_chk else '-' end as fcc6 ,
case RANK when 7 then filter_code else '-' end as fc7, case RANK when 7 then filter_chk else '-' end as fcc7
FROM
(
SELECT ORG_ID_NO, CE_SEQ_NO, FILTER_CODE, FILTER_CHK, ISNULL(
(
SELECT COUNT(*) FROM FILTER_RESULT
WHERE X.FILTER_CODE >= FILTER_CODE AND ORG_ID_NO= X.ORG_ID_NO
) , 1) AS RANK
FROM FILTER_RESULT X
GROUP BY ORG_ID_NO, CE_SEQ_NO, FILTER_CODE, FILTER_CHK
)Y
)Z
GROUP BY ORG_ID_NO, CE_SEQ_NO
2. 오라클로 했을 경우
select org_id_no, ce_seq_no, max(fc1) fc1, max(fcc1) fcc1, max(fc2) fc2, max(fcc2) fcc2, max(fc3) fc3, max(fcc3) fcc3,
max(fc4) fc4, max(fcc4) fcc4, max(fc5) fc5, max(fcc5) fcc5, max(fc6) fc6, max(fcc6) fcc6,
max(fc7) fc1, max(fcc7) fcc7
from
(
select org_id_no, ce_seq_no,
decode(rn,1,filter_code,'-') fc1,
decode(rn,1,filter_chk,'-') fcc1,
decode(rn,2,filter_code,'-') fc2,
decode(rn,2,filter_chk,'-') fcc2,
decode(rn,3,filter_code,'-') fc3,
decode(rn,3,filter_chk,'-') fcc3,
decode(rn,4,filter_code,'-') fc4,
decode(rn,4,filter_chk,'-') fcc4,
decode(rn,5,filter_code,'-') fc5,
decode(rn,5,filter_chk,'-') fcc5,
decode(rn,6,filter_code,'-') fc6,
decode(rn,6,filter_chk,'-') fcc6,
decode(rn,7,filter_code,'-') fc7,
decode(rn,7,filter_chk,'-') fcc7
from
(
select org_id_no, ce_seq_no, filter_code, filter_chk,
row_number() over(partition by org_id_no order by to_number(replace(filter_code,'FT',''))) rn
from filter_result
)
)
group by org_id_no, ce_seq_no
이렇다. 이게 맞는 답인지는 아무도 모른다. 더 최적화 시킬수도 있을터이니
답부터 보지말고 문제를 풀어보고 내가 푼 답은 참고용으로만...