SQL查询:如何从一个col与另一个col配对中获取项目，反之亦然 [英] SQL Query: How to get items from one col paired with another but not vice versa

问题描述

我需要一个查询，该查询返回与colB配对的colA中的所有行，但将相反方向上的相同值视为重复项并删除.

I need a query that returns all the rows from colA paired with colB but to treat the same values in the opposite direction as duplicates and to be removed.

解释此查询的最佳方法是通过示例:

The best way to explain this query is by example:

colA | colB
-----------
abc  | def
def  | abc
asdf | 1234
1234 | asdf
other| row
1234 | test

SQL MAGIC

SQL MAGIC

colA | colB
-----------
abc  | def
asdf | 1234
other| row
1234 | test

它将删除在另一个方向上重复"的行.

It removes the rows which are 'duplicate' in the other direction.

推荐答案

如果您喜欢干净的" SQL解决方案(没有least()或greatest())，这也可以完成您的工作:

If you prefer a "clean" SQL solution (without least() or greatest()) this also does your job:

select colA, colB from your_table
where colA > colB 
  or (colB, colA) not in (select colA, colB from your_table)

SQL提琴

这篇关于SQL查询:如何从一个col与另一个col配对中获取项目，反之亦然的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！