weixin_48713884
慵懒的小時光
采纳率0%
2021-05-09 17:16 阅读 135

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

50

原始数据和处理后数据输出如下(用标准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
你好	好我
你好	我好
你	好我好
你好我	好
  • 点赞
  • 收藏
  • 复制链接分享

5条回答 默认 最新

  • software7503 CSDN专家-赖老师(软件之家) 2021-05-09 17:24

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

    点赞 评论 复制链接分享
  • software7503 CSDN专家-赖老师(软件之家) 2021-05-09 18:01

    查询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条语句的结果合并

    点赞 评论 复制链接分享
  • weixin_48713884 慵懒的小時光 2021-05-09 21:02

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

    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
    点赞 评论 复制链接分享
  • QA_Assistant 有问必答小助手 2021-05-10 10:58

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

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

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

    点赞 评论 复制链接分享
  • QA_Assistant 有问必答小助手 2021-05-11 15:47

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

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

    点赞 评论 复制链接分享

相关推荐