数据库查询结果处理：去除两个字段交换后相同的数据

两个表均有AB字段
select A,B,A||B from 表1
union all
select A,B,A||B from 表2
查询结果是这样：
A B 拼接字段
a b ab
a c ac
a d ad
c d cd
c a ca
b a ba

目的就是要去除像最后两条这样的数据
也就是认为A字段和B字段数据交换之后和他本身是一个数据，即ab和ba是同一个数据
oracle数据库，求解决方法

 select * from SELECT 拼接字段 FROM 表 ORDER BY CASE WHEN CAST(PARSENAME(拼接字段,2) AS int) IS NULL THEN 1 ELSE 0 END,CAST(PARSENAME(拼接字段,2) AS int), PARSENAME(拼接字段,2) group by 拼接字段

SELECT A,B,CONCAT(A,B) 拼接字段 FROM 表1 UNION ALL SELECT A,B,CONCAT(A,B) 拼接字段 FROM 表2 GROUP BY CONCAT(A,B) || CONCAT(B,A)

SELECT a,b,CONCAT(a,b) from A AS a
WHERE  CONCAT(a,b) not in (SELECT CONCAT(a1.b,a1.a) FROM A as a2)

这个是简单的查询一张表A里面的去除颠倒重复的，你是两张表，在union一个一样语法表B的操作就可以了

如果你用的是oracle的话,oracle有一个reverse() 字符串反转函数, select reverse('ab') from dual; 结果是: ba 你可以思考下用这个函数来进行筛选.
最无脑的是,你把每行数据的A,B列组合以及他的reverse()后的组合放到一起,然后select distinct..比如像下面这样:
select distinct t.* from (
select A||B from 表1
union all
select reverse(A||B) from 表1
union all
select A||B from 表2
union all
select reverse(A||B) from 表2
) t

用union all,而不是子语句的话,虽然看起来写得很臃肿,但是效率不至于差太多.

需要关注的是,reverse() 函数不支持中文的反转.

如果是用的其他数据库,当我没说.不过其实也可以自己写一个反转的函数来实现.反转的函数写法就是把字符串转成字符,然后倒过来输出.

with tt as (
select A,B,A||B as C from t0 a where not exists (select 1 from t0 b where b.A||b.B=a.B||a.A)
union all
select A,B,A||B as C from t1 a where not exists (select 1 from t1 b where b.A||b.B=a.B||a.A))
select * from tt
minus
select * from tt group by A,B,C having count(1)>1
minus
select * from (select A,B,A||B as C from t0 union all select B,A,B||A as C from t1)
group by A,B,C having count(1)>1
minus
select * from (select B,A,B||A as C from t0 union all select A,B,A||B as C from t1)
group by A,B,C having count(1)>1