如何利用标准高效查询SQL实现单表去重A与B字段的交叉值

原始数据和处理后数据输出如下（用标准SQL语言实现）：

提供可验证正确的SQL。

补充数据

select 'a' as col1,'b' as col2 from dual union all
select 'b' as col1,'a' as col2 from dual union all

select 'b' as col1,'c' as col2 from dual union all

select 'b' as col1,'d' as col2 from dual union all

select 'ba' as col1,'ab' as col2 from dual union all
select 'ab' as col1,'ba' as col2 from dual union all

select 'ba' as col1,'bd' as col2 from dual union all

select '你好' as col1,'我好' as col2 from dual union all
select '我好' as col1,'你好' as col2 from dual union all

select '你好我' as col1,'好' as col2 from dual union all
select '你' as col1,'好我好' as col2 from dual union all

select '你好' as col1,'好我' as col2 from dual

预期结果：

COL1	COL2
a	b
b	c
b	d
ab	ba
ba	bd
你好	好我
你好	我好
你	好我好
你好我	好

是a字段或b字段里面出现相同的只留一个吗？

查询2次：

select  * from test t
where not exists (select * from test where a=t.b and b=t.a)；

和

select  * from test t
where exists (select * from test where a=t.b and b=t.a) group by a,b limit 0,1；

2条语句的结果合并

下面是一种参考方法，求更多解题方法

with tmp as(select 'a' as col1,'b' as col2 from dual union all
select 'b' as col1,'a' as col2 from dual union all

select 'b' as col1,'c' as col2 from dual union all

select 'b' as col1,'d' as col2 from dual union all

select 'ba' as col1,'ab' as col2 from dual union all
select 'ab' as col1,'ba' as col2 from dual union all

select 'ba' as col1,'bd' as col2 from dual union all

select '你好' as col1,'我好' as col2 from dual union all
select '我好' as col1,'你好' as col2 from dual union all

select '你好我' as col1,'好' as col2 from dual union all
select '你' as col1,'好我好' as col2 from dual union all

select '你好' as col1,'好我' as col2 from dual),
tmp1 as(select  row_number() over (partition by (to_number(rawtohex(t1.col1),'XXXXXXXXXXXXXXXXXXXXX')+to_number(rawtohex(t1.col2),'XXXXXXXXXXXXXXXXXXXXX'))
 order by t1.col1) rn,
t1.col1,t1.col2 from tmp t1 left join tmp t2 on t1.col1=t2.col2 and t1.col2=t2.col1
)
select * from tmp1 where rn=1

您好，我是有问必答小助手，您的问题已经有小伙伴解答了，您看下是否解决，可以追评进行沟通哦~

如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～

ps:问答VIP仅需29元，即可享受5次/月 有问必答服务，了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632

非常感谢您使用有问必答服务，为了后续更快速的帮您解决问题，现诚邀您参与有问必答体验反馈。您的建议将会运用到我们的产品优化中，希望能得到您的支持与协助！

速戳参与调研>>>https://t.csdnimg.cn/Kf0y