select col_a,col_b from tab minus select col_b,col_a from tab where col_b>col_a;
with test as ( select '1' as col_a , '2' as col_b from dual union all select '1' as col_a , '3' as col_b from dual union all select '2' as col_a , '3' as col_b from dual union all select '3' as col_a , '1' as col_b from dual ), result_list as ( select a.col_a as a , a.col_b as b ,b.col_a , b.col_b from test a ,test b where a.col_a != b.col_b and a.col_b != a.col_a minus select a.col_a as a , a.col_b as b ,b.col_a , b.col_b from test a ,test b where a.col_a = b.col_a and a.col_b = a.col_b ) select a, b from ( select a,b,row_number()over(partition by a,b order by a,b) rn from result_list ) where rn = 1
上面的SQL文貌似有点问题 现在改一下 with test as ( select '1' as col_a , '2' as col_b from dual union all select '1' as col_a , '3' as col_b from dual union all select '2' as col_a , '3' as col_b from dual union all select '3' as col_a , '1' as col_b from dual ), result_list as ( select a.col_a ,a.col_b from test a , test b where a.col_a = b.col_b and a.col_b = b.col_a and b.col_a < a.col_a ) select col_a , col_b from ( select a.col_a,a.col_b,nvl(b.col_a,1) as flg from test a left join result_list b on a.col_a = b.col_a and a.col_b = b.col_b ) where flg = 1 order by col_a
select col_a,col_b from tab
minus
select col_b,col_a from tab where col_b>col_a;
with
test as
(
select '1' as col_a , '2' as col_b from dual
union all
select '1' as col_a , '3' as col_b from dual
union all
select '2' as col_a , '3' as col_b from dual
union all
select '3' as col_a , '1' as col_b from dual
),
result_list as
(
select a.col_a as a , a.col_b as b ,b.col_a , b.col_b from test a ,test b
where a.col_a != b.col_b and a.col_b != a.col_a
minus
select a.col_a as a , a.col_b as b ,b.col_a , b.col_b from test a ,test b
where a.col_a = b.col_a and a.col_b = a.col_b
)
select a, b from
(
select a,b,row_number()over(partition by a,b order by a,b) rn from result_list
)
where rn = 1
with
test as (
select '1' as col_a , '2' as col_b from dual
union all
select '1' as col_a , '3' as col_b from dual
union all
select '2' as col_a , '3' as col_b from dual
union all
select '3' as col_a , '1' as col_b from dual
),
result_list
as (
select a.col_a ,a.col_b from test a , test b where
a.col_a = b.col_b and a.col_b = b.col_a and b.col_a < a.col_a
)
select col_a , col_b from
(
select a.col_a,a.col_b,nvl(b.col_a,1) as flg from test a left join
result_list b on a.col_a = b.col_a and a.col_b = b.col_b
)
where flg = 1
order by col_a