MySQL 选择 UNION 作为 DISTINCT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8782786/
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
SELECT UNION as DISTINCT
提问by user1124535
How do I perform a DISTINCT operation on a single column after a UNION is performed?
执行 UNION 后如何对单个列执行 DISTINCT 操作？
T1
--
ID Value
1 1
2 2
3 3
T2
--
ID Value
1 2
4 4
5 5
I am trying to return the table:
我正在尝试返回表：
ID Value
1 1
2 2
3 3
4 4
5 5
I tried:
我试过：
SELECT DISTINCT ID, Value
FROM (SELECT*FROM T1 UNION SELECT*FROM T2) AS T3
This does not seem to work.
这似乎不起作用。
回答by Bohemian
Why are you using a sub-query? This will work:
为什么要使用子查询？这将起作用：
SELECT * FROM T1
UNION
SELECT * FROM T2
UNIONremoves duplicates. (UNION ALLdoes not)
UNION删除重复项。（UNION ALL不）
回答by alf
As far as I can say, there's no "one-column distinct": distinctis always applied to a whole record (unless used within an aggregate like count(distinct name)). The reason for this is, SQL cannot guess which values of Valueto leave for you—and which to drop. That's something you need to define by yourself.
据我所知，没有“一列distinct”：distinct总是应用于整个记录（除非在像 那样的聚合中使用count(distinct name)）。这样做的原因是，SQL 无法猜测哪些值Value要留给您，哪些值要删除。这是你需要自己定义的东西。
Try using GROUP BYto ensure IDis not repeated, and any aggregate (here MIN, as in your example it was the minimum that survived) to select a particular value of Value:
尝试使用GROUP BY以确保 ID不重复，并且任何聚合（这里MIN，如在您的示例中，它是幸存下来的最小值）来选择 的特定值Value：
SELECT ID, min(Value) FROM (SELECT * FROM T1 UNION ALL SELECT * FROM T2) AS T3
GROUP BY ID
Should be exactly what you need. That is, it's not the same query, and there's no distinct—but it's a query which would return what's shown in the example.
应该正是您所需要的。也就是说，它不是同一个查询，也没有distinct——但它是一个将返回示例中所示内容的查询。
回答by liselorev
This - even though this thread is way old - might be a working solution for the question of the OP, even though it might be considered dirty.
这 - 即使这个线程很老 - 可能是 OP 问题的有效解决方案，即使它可能被认为是肮脏的。
We select all tuples from the first table, then adding (union) it with the tuples from the second table limited to those that doe not have the specific field matched in the first table.
我们从第一个表中选择所有元组，然后将它与第二个表中的元组添加（联合）到那些没有匹配第一个表中的特定字段的元组。
SELECT *
FROM T1
UNION
SELECT *
FROM T2
WHERE (
Value NOT IN (SELECT Value FROM T1)
);
回答by KT8
I think that's what you meant:
我想这就是你的意思：
SELECT *
FROM T1
UNION
SELECT *
FROM T2
WHERE (
**ID
** NOT IN (SELECT ID FROM T1)
);
