-- stand 기준이 2일 경우 ALL 만 with t as ( select 'GB' code, 'AAA' nm, '18/1' day, 'ALL' stand from dual union all select 'GB' code, 'AAA' nm, '18/1' day, '2' stand from dual) select * from t where nm = :nm and day = :day and stand = decode(:stand, '2', 'ALL') -- stand 기준이 2일 경우 2를 제외한 전체 with t as ( select 'GB' code, 'AAA' nm, '18/1' day, 'ALL' stand from dual union all select 'GB' code, 'AAA' nm, '18/1' day, '5' stand from dual union all select 'GB' code, 'AAA' nm, '18/1' day, '2' stand from dual) select * from t where nm = :nm and day = :day and stand <> decode(:stand, '2', :stand) -- stand '2' 제외 with t as ( select 'GB' code, 'AAA' nm, '18/1' day, 'ALL' stand from dual union all select 'GB' code, 'AAA' nm, '18/1' day, '2' stand from dual) select * from t where nm = :nm and day = :day and stand <> '2' --(stand 제외 기준이 늘어나면 not in ('2', '3')) -- stand 기준이 ALL with t as ( select 'GB' code, 'AAA' nm, '18/1' day, 'ALL' stand from dual union all select 'GB' code, 'AAA' nm, '18/1' day, '2' stand from dual) select * from t where nm = :nm and day = :day and stand = 'ALL'
WITH code_t AS ( SELECT 'GBA' code, 'AAA' name, '18/1' day, 'ALL' stand FROM dual UNION ALL SELECT 'GB2', 'AAA', '18/1', '2' FROM dual UNION ALL SELECT 'GB1', 'AAA', '18/1', '1' FROM dual ) , data_t AS ( SELECT 'AAA' name, '18/1' day, '12' stand FROM dual UNION ALL SELECT 'AAA', '18/1', '1' FROM dual UNION ALL SELECT 'AAA', '18/1', '2' FROM dual UNION ALL SELECT 'AAA', '18/1', '3' FROM dual ) SELECT a.name, a.day, a.stand , NVL(b.code, c.code) code FROM data_t a , code_t b , code_t c WHERE b.name (+) = a.name AND b.day (+) = a.day AND b.stand(+) = a.stand AND c.name (+) = a.name AND c.day (+) = a.day AND c.stand(+) = 'ALL' ;