-- 입고, 취소량이 같다고 가정 with t("발생일자","거래처","수량","입/취소여부") as ( select '20000101','A',100,'Y' from dual union all select '20000101','B',500,'Y' from dual union all select '20000101','B',500,'N' from dual union all select '20000101','B',300,'Y' from dual union all select '20000101','C',150,'Y' from dual ) select "발생일자", "거래처", "수량", "입/취소여부", decode(count(*) over (partition by seq, "발생일자", "거래처", "수량"), 1, '추출대상') 대상여부 from ( select row_number() over (partition by "발생일자", "거래처", "수량", "입/취소여부" order by "입/취소여부") seq, t.* from t ) v order by "발생일자", "거래처", "수량" desc, "입/취소여부" desc
with t("발생일자","거래처","수량","입/취소여부") as ( select '20000101','A',100,'Y' from dual union all select '20000101','B',500,'Y' from dual union all select '20000101','B',500,'N' from dual union all select '20000101','B',300,'Y' from dual union all select '20000101','C',150,'Y' from dual ) select * from t where ("발생일자","거래처","수량") not in ( select "발생일자","거래처","수량" from t where "입/취소여부" = 'N' ) ;