데이터베이스 질문과 답변
WITH EXAMPLE AS
(
SELECT 'NO1' AS ID , '1' AS LV1 , '11' AS LV2 FROM DUAL UNION
SELECT 'NO2' AS ID , '1' AS LV1 , '12' AS LV2 FROM DUAL UNION
SELECT 'NO3' AS ID , '1' AS LV1 , '13' AS LV2 FROM DUAL UNION
SELECT 'NO4' AS ID , '2' AS LV1 , '21' AS LV2 FROM DUAL UNION
SELECT 'NO5' AS ID , '2' AS LV1 , '22' AS LV2 FROM DUAL UNION
SELECT 'NO6' AS ID , '3' AS LV1 , '31' AS LV2 FROM DUAL
)
SELECT * FROM EXAMPLE
데이터는 위와같은 형태입니다..
검색조건은 LV1과 LV2가 넘어오는데
LV1 검색조건이 우선으로 검색되고 LV2는 있으면 포함해서 조회되게 하고싶습니다..
즉 검색조건이
LV1 '1','2'
LV2 '11','12'
가 넘어왔을때..
결과를
ID LV1 LV2
1 1 11
2 1 12
4 2 21
5 2 22
위처럼
LV1 1,2를 가져온상태에서.. LV2가 11 , 12 ... 즉 13은 제외하고 가져오고싶은데
조건절을 어떻게 구성해야할까요
조언 부탁드립니다.
WHERE LV1 IN ('1','2') OR LV2 IN ( '11' , '12')이런식으로는 안되네요 ㅜㅜ
도움부탁드려요
WITH example AS
(
SELECT 'NO1' id, '1' lv1, '11' lv2 FROM dual
UNION ALL SELECT 'NO2', '1', '12' FROM dual
UNION ALL SELECT 'NO3', '1', '13' FROM dual
UNION ALL SELECT 'NO4', '2', '21' FROM dual
UNION ALL SELECT 'NO5', '2', '22' FROM dual
UNION ALL SELECT 'NO6', '3', '31' FROM dual
)
SELECT *
FROM (SELECT id, lv1, lv2
, (CASE WHEN lv2 IN ('11', '12') THEN 1 END) chk
, COUNT(CASE WHEN lv2 IN ('11', '12') THEN 1 END) OVER(PARTITION BY lv1) cnt
FROM example
WHERE lv1 IN ('1', '2')
)
WHERE chk = 1
OR cnt = 0
;
큰도움 되었습니다 매번 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.