MYSQL 选择两列中的 DISTINCT 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6744759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL select DISTINCT values in two columns
提问by Jake
I want to select distinct values in a database. Let me run you through a quick example.
我想在数据库中选择不同的值。让我通过一个简单的示例为您介绍。
Table:
桌子：
foo bar
--- ---
a c
c f
d a
c a
f c
a c
d a
a c
c a
f c
Right, let's say my SQL is SELECT DISTINCT foo, bar from table. These are my results:
是的，假设我的 SQL 是SELECT DISTINCT foo, bar from table. 这些是我的结果：
foo bar
--- ---
a c
c f
d a
c a
f c
However the problem is is that there are repetitions of a c/ c ajust that they are in a different order. I don't want to select these, I want distinct values from both columns, please help!
然而问题是有重复的a c/c a只是它们的顺序不同。我不想选择这些，我想要两列不同的值，请帮忙！
采纳答案by nabuchodonossor
very very wicked & evil:
非常非常邪恶和邪恶：
select distinct
least(foo, bar) as value1
, greatest(foo, bar) as value2
from table
回答by Yaniv
How about using GROUP BY?
使用 GROUP BY 怎么样？
SELECT foo,bar FROM my_table GROUP BY foo,bar
SELECT foo,bar FROM my_table GROUP BY foo,bar
回答by Kevin Burton
How about :
怎么样 ：
SELECT DISTINCT a.foo,a.bar
FROM table a
LEFT JOIN table b ON a.foo=b.bar and a.bar=b.foo
WHERE b.foo IS NULL AND b.bar IS NULL
Output :
输出 ：
foo bar
--- ---
d a
回答by outis
You're asking for something that's the opposite of a symmetric closure (I don't know if it has a special name; antisymmetric something, since it's not a closure). For closures and closure like things where you need to compare two different columns, you can use joins. To make sure you don't filter out both rows when they are duplicated across columns, you need to a way to differentiate the repeats and include one of them, such as by including the pair where the first is the lesser.
您要求的东西与对称闭包相反（我不知道它是否有特殊名称；反对称的东西，因为它不是闭包）。对于需要比较两个不同列的闭包和闭包之类的东西，您可以使用连接。为了确保在跨列重复时不会过滤掉两行，您需要一种方法来区分重复并包括其中一个，例如通过包括第一对较小的对。
SELECT DISTINCT t1.foo, t1.bar
FROM `table` t1
LEFT JOIN `table` t2
ON t1.foo=t2.bar AND t1.bar=t2.foo
WHERE t2.foo IS NULL OR t1.foo <= t1.bar;
回答by ypercube??
SELECT
foo, bar
FROM tableX
WHERE foo <= bar
UNION
SELECT
bar, foo
FROM tableX
WHERE bar < foo
回答by Talha Ahmed Khan
SELECT DISTINCT foo, bar FROM table WHERE
CONCAT(',',foo,bar,) NOT IN ( SELECT CONCAT(',',bar,foo) FROM table )
回答by bfortuner
This works for me:
这对我有用：
SELECT DISTINCT
LEAST(sub.foo, sub.bar) as value_1
, GREATEST(sub.foo, sub.bar) as value_2
FROM
(SELECT
a.foo
,a.bar
FROM
table a
JOIN
table b
on a.foo = b.bar
and a.bar = b.foo) sub
回答by M A
So what I understood you want to combine 2 fields into a single SQL search result rows... I found most answers here very confusing.
所以我理解你想要将 2 个字段组合成一个 SQL 搜索结果行......我发现这里的大多数答案都非常令人困惑。
This is the way I always do it when I have to (can be scaled up with multiple rows and multiple tables):
这是我在必须时总是这样做的方式（可以扩展多行和多个表）：
SELECT a.field1 FROM (<br>
SELECT foo AS field1 FROM tableX<br>
UNION<br>
SELECT bar AS field1 FROM tableX<br>
) a
OR
SELECT DISTINCT a.field1 FROM (<br>
SELECT foo AS field1, conditions FROM tableX<br>
UNION ALL<br>
SELECT bar AS field1, conditions FROM tableX<br>
) a WHERE a.conditions=1 (added extra conditions to filter out some results)
