如何在SQL中查找两列中的唯一对？

Question

我有一张名为“Dummy”的表，其中包含“col1”和“col2”两列。

如何从（col1，col2）中找到唯一的对？例如，在上面的表格中，如何仅获取（a，b）或（b，a）作为输出，而不是同时获取（a，b）和（b，a）。

select
    distinct
    col1
    col2
from
    dummy
where
    dummy.col1 < dummy.col2
group by
    col1,
    col2;

上述查询是错误的，因为它遗漏了(d,c)这一对。

- Parth Parikh

---

我认为你想要的是 WHERE col1 <> col2，而不是 WHERE col1 < col2。 - Dai

你需要获取现有的 d,c 还是 c,d 也可以？ - dnoeth

在这种情况下，您会得到a，d和d，a。 - lurker

嗨。这是一个常见问题解答。你本可以通过谷歌搜索来解决你的问题。 请使用多个清晰、简洁、具体的版本/变体/措辞来搜索你的问题/目标/需求，包括和不包括你的特定字符串/名称/代码以及“stackoverflow”，并阅读许多问题的答案，这将有助于你进一步的搜索。如果你在应用所学知识并重复搜索后仍然找不到答案，请提出问题。使用最常用的关键字作为标签。使用最佳搜索结果作为标题。 - philipxy

可能是[获取两个字段值的唯一组合]的重复问题(https://dev59.com/NEzSa4cB1Zd3GeqPmGGV)。 - philipxy

Answer 1

我更倾向于使用concat和group by。

SELECT 
col1,col2 
FROM 
tbl
GROUP BY CONCAT(LEAST(col1, col2), 
              GREATEST(col1, col2))

或者简单地说

SELECT 
    col1,col2 
    FROM 
    tbl
group by LEAST(col1, col2),GREATEST(col1, col2)

- sumit

---

当然，这只适用于MySQL，在其他数据库管理系统或启用了ONLY_FULL_GROUP_BY时将会失败。 - dnoeth

Answer 2

如果您不关心返回的现有组合是否以正确的顺序返回，例如a,b也可能被返回为b,a：

SELECT DISTINCT
  CASE WHEN col1 > col2 THEN col2 ELSE col1 end, -- similar to LEAST
  CASE WHEN col1 < col2 THEN col2 ELSE col1 end  -- similar to GREATEST
FROM dummy
;

但是如果你必须返回一个已经存在的行，这就更加复杂了:

SELECT t1.*
FROM dummy AS t1 LEFT JOIN dummy AS t2
ON  t1.col1 = t2.col2
AND t1.col2 = t2.col1
AND t1.col1 > t2.col1
WHERE t2.col1 IS NULL
;

SELECT col1,col2
FROM dummy AS t1
WHERE NOT EXISTS(
  SELECT * FROM dummy t2
  WHERE t1.col1 = t2.col2
    AND t1.col2 = t2.col1
    AND t1.col1 > t2.col1
);

- dnoeth

Answer 3

SELECT
    x,
    y
FROM
(

    SELECT
        DISTINCT
        col1 AS x,
        col2 AS y
    FROM
        dummy
    WHERE
        col1 <> col2

    UNION

    SELECT
        DISTINCT
        col1 AS y,
        col2 AS x
    FROM
        dummy
    WHERE
        col1 <> col2
)

- Dai

---

为什么要使用外部Select，而且当UNION已经是唯一的时候，为什么还要使用两个DISTINCT？ - dnoeth

Answer 4

使用 least 和 greatest。

select least(col1,col2),greatest(col1,col2) 
from tbl
group by least(col1,col2),greatest(col1,col2) 

但是如果只存在一对（x，y）或（y，x），则可能返回不在表中的行。

为了避免这种情况，请使用

select least(col1,col2) as col1,greatest(col1,col2) as col2
from tbl
group by least(col1,col2),greatest(col1,col2) 
having count(*)>1
union all
select col1,col2 
from tbl
where (least(col1,col2),greatest(col1,col2)) in (select least(col1,col2) as col1,greatest(col1,col2) as col2
                                                 from tbl
                                                 group by least(col1,col2),greatest(col1,col2) 
                                                 having count(*)=1
                                                )    

- Vamsi Prabhala