데이터베이스 질문과 답변
데이터값
| AAAA | BBBB | CCCC |
| 111111 | 2 | NIKE SHOES |
| 111111 | 3 | NIKE CLEAR |
| 222222 | 2 | ADIDAS TEST |
| 222222 | 3 | CANCEL ADIDAS |
| 333333 | 2 | CLEAR ASICE |
| 333333 | 3 | ASICE SALE |
| 444444 | 2 | PUMA CLEAR |
| 444444 | 3 | CANCEL PUMA |
| 555555 | 2 | CLEAR PROSPECT |
출력값
| AAAA | BBBB | CCCC |
| 111111 | 2 | NIKE SHOES |
| 222222 | 2 | ADIDAS TEST |
| 333333 | 3 | ASICE SALE |
| 444444 | 3 | CANCEL PUMA |
| 555555 | 2 | CANCEL PROSPECT |
AAAA, BBBB, CCCC 3개의 컬럼이 있습니다
primary key = AAAA, BBBB 컬럼이구여
AAAA 컬럼 기준으로 BBBB컬럼 2와3을 비교하여 CLEAR또는 CANCEL 이란 단어가 있을시 다른행을 반화하여 표출하여야 합니다.
2와 3 컬럼 비교했을시 CLEAR또는 CANCEL 이란 단어가 둘다 존재하면 BBBB컬럼 값이 3인 값을 표출해야합니다.
만약에 AAAA 컬럼값이 한개이고 BBBB컬럼이 2만 존재하면 2인값을 표출해야합니다
어떻게 해야될까요??
데이터양이 많이 좀 많은데 어떻게 뽑아야될지 알려주세요~
WITH T AS (
SELECT '111111' AS AAAA, '2' AS BBBB, 'NIKE SHOES' AS CCCC FROM DUAL UNION ALL
SELECT '111111' AS AAAA, '3' AS BBBB, 'NIKE CLEAR' AS CCCC FROM DUAL UNION ALL
SELECT '222222' AS AAAA, '2' AS BBBB, 'ADIDAS TEST' AS CCCC FROM DUAL UNION ALL
SELECT '222222' AS AAAA, '3' AS BBBB, 'CANCEL ADIDAS' AS CCCC FROM DUAL UNION ALL
SELECT '333333' AS AAAA, '2' AS BBBB, 'CLEAR ASICE' AS CCCC FROM DUAL UNION ALL
SELECT '333333' AS AAAA, '3' AS BBBB, 'ASICE SALE' AS CCCC FROM DUAL UNION ALL
SELECT '444444' AS AAAA, '2' AS BBBB, 'PUMA CLEAR' AS CCCC FROM DUAL UNION ALL
SELECT '444444' AS AAAA, '3' AS BBBB, 'CANCEL PUMA' AS CCCC FROM DUAL UNION ALL
SELECT '555555' AS AAAA, '2' AS BBBB, 'CANCEL PROSPECT' AS CCCC FROM DUAL
)
select aaaa, bbbb, cccc from ( select aaaa, bbbb, cccc , row_number() over(partition by aaaa order by dddd, bbbb desc) rn from ( select aaaa, bbbb, cccc , regexp_count(cccc,'CLEAR|CANCEL') dddd from t ) ) where rn = 1
WITH tttt AS
(
SELECT 111111 aaaa, 2 bbbb, 'NIKE SHOES' cccc FROM dual
UNION ALL SELECT 111111, 3, 'NIKE CLEAR' FROM dual
UNION ALL SELECT 222222, 2, 'ADIDAS TEST' FROM dual
UNION ALL SELECT 222222, 3, 'CANCEL ADIDAS' FROM dual
UNION ALL SELECT 333333, 2, 'CLEAR ASICE' FROM dual
UNION ALL SELECT 333333, 3, 'ASICE SALE' FROM dual
UNION ALL SELECT 444444, 2, 'PUMA CLEAR' FROM dual
UNION ALL SELECT 444444, 3, 'CANCEL PUMA' FROM dual
UNION ALL SELECT 555555, 2, 'CLEAR PROSPECT' FROM dual
)
SELECT aaaa, bbbb, cccc
FROM (SELECT aaaa, bbbb, cccc
, ROW_NUMBER() OVER(PARTITION BY aaaa
ORDER BY SIGN(REGEXP_COUNT(cccc, 'CLEAR|CANCEL')), bbbb DESC) rn
FROM tttt
WHERE bbbb IN (2, 3)
)
WHERE rn = 1
;
bbbb 컬럼에 3만 존재하면 어떻게 되는건가요?
bbb컬럼에 3만 존재시에는 3번값을 출력해야 합니다
) 버튼을 클릭하여 작성 하시면 됩니다.