blocks|key|2460472|text|使用least和greatest。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2460473|select+least(col1,col2),greatest(col1,col2)+
from+tbl
group+by+least(col1,col2),greatest(col1,col2)+|code-block|syntax|javascript|2460474|但是如果只有一对(x，y)或(y，x)存在，这可能会返回表中没有的行。|2460475|要避免这种情况，请使用|2460476|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
++++++++++++++++++++++++++++++++++++++++++++++++)++++|2460477|entityMap^0|2|5|8|8|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|Q|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|R|$]]

Use <code>least</code> and <code>greatest</code>.

<pre><code>select least(col1,col2),greatest(col1,col2) 
from tbl
group by least(col1,col2),greatest(col1,col2) 
</code></pre>

But this may return rows that aren't in the table if only one pair of (x,y) or (y,x) exists.

To avoid that, use

<pre><code>select least(col1,col2) as col1,greatest(col1,col2) as col2
from tbl
group by least(col1,col2),greatest(col1,col2) 
having count(*)&gt;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
 ) 
</code></pre>

blocks|key|2467142|text|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
)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2467143|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT
 x,
 y
FROM
(

 SELECT
 DISTINCT
 col1 AS x,
 col2 AS y
 FROM
 dummy
 WHERE
 col1 &lt;&gt; col2

 UNION

 SELECT
 DISTINCT
 col1 AS y,
 col2 AS x
 FROM
 dummy
 WHERE
 col1 &lt;&gt; col2
)
</code></pre>

blocks|key|5803875|text|我更喜欢使用concat和group+by|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5803876|SELECT+
col1,col2+
FROM+
tbl
GROUP+BY+CONCAT(LEAST(col1,+col2),+
++++++++++++++GREATEST(col1,+col2))|code-block|syntax|javascript|5803877|或者简单地说|5803878|SELECT+
++++col1,col2+
++++FROM+
++++tbl
group+by+LEAST(col1,+col2),GREATEST(col1,+col2)|5803879|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

i would rather use concat and group by

<pre><code>SELECT 
col1,col2 
FROM 
tbl
GROUP BY CONCAT(LEAST(col1, col2), 
 GREATEST(col1, col2))
</code></pre>

OR SIMPLY

<pre><code>SELECT 
 col1,col2 
 FROM 
 tbl
group by LEAST(col1, col2),GREATEST(col1, col2)
</code></pre>

blocks|key|547928|text|如果您不关心现有的组合是否以正确的顺序返回，例如，a,b也可能以b,a的形式返回|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|547929|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
;|code-block|syntax|javascript|547930|但是如果你必须返回一个现有的行，那就更复杂了：|547931|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
);|547932|entityMap^0|P|3|W|3|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

If you don't care if an existing combination is returned in the right order, e.g. <code>a,b</code> might also be returned as<code>b,a</code>:

<pre><code>SELECT DISTINCT
 CASE WHEN col1 &gt; col2 THEN col2 ELSE col1 end, -- similar to LEAST
 CASE WHEN col1 &lt; col2 THEN col2 ELSE col1 end -- similar to GREATEST
FROM dummy
;
</code></pre>

But if you must return an existing row it' more complicated:

<pre><code>SELECT t1.*
FROM dummy AS t1 LEFT JOIN dummy AS t2
ON t1.col1 = t2.col2
AND t1.col2 = t2.col1
AND t1.col1 &gt; 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 &gt; t2.col1
);
</code></pre>

i have a table"Dummy" with columns "col1 and col2".

How do i find unique pairs from(col1,col2). For example in the above table how do i get (a,b) or (b,a) only as my output, instead of both (a,b) and (b,a).

<pre><code>select
 distinct
 col1
 col2
from
 dummy
where
 dummy.col1 &lt; dummy.col2
group by
 col1,
 col2;
</code></pre>

the above query is wrong as it missed out the pair (d,c).

<a href="https://i.stack.imgur.com/MkE36.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MkE36.png" alt="Wrong query"></a>

How to find unique pairs from two columns in SQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个表“Dummy”，其中包含列"col1“和”col2“。如何从(col1，col2)中找到唯一对。例如，在上表中，我如何仅获得(a，b)或(b，a)作为我的输出，而不是同时获得(a，b)和(b，a)。select    distinct    col1    col2from    dummywhere    ...

问如何从SQL中的两列中找到唯一对？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从SQL中的两列中找到唯一对？EN