从 sql union 中删除重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4127714/
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
remove duplicates from sql union
提问by thegunner
I'm doing some basic sql on a few tables I have, using a union(rightly or wrongly)
我正在使用联合（正确或错误）对我拥有的几个表执行一些基本的 sql
but I need remove the duplicates. Any ideas?
但我需要删除重复项。有任何想法吗？
select * from calls
left join users a on calls.assigned_to= a.user_id
where a.dept = 4
union
select * from calls
left join users r on calls.requestor_id= r.user_id
where r.dept = 4
回答by Randy Minder
Unionwill remove duplicates. Union Alldoes not.
Union将删除重复项。Union All才不是。
回答by Jeremy Elbourn
Using UNIONautomatically removes duplicate rows unless you specify UNION ALL:
http://msdn.microsoft.com/en-us/library/ms180026(SQL.90).aspx
UNION除非您指定，否则使用会自动删除重复行UNION ALL：http:
//msdn.microsoft.com/en-us/library/ms180026(SQL.90).aspx
回答by Jerry Coffin
Others have already answered your direct question, but perhaps you could simplify the query to eliminate the question (or have I missed something, and a query like the following will really produce substantially different results?):
其他人已经回答了您的直接问题，但也许您可以简化查询以消除问题（或者我错过了什么，像下面这样的查询真的会产生完全不同的结果？）：
select *
from calls c join users u
on c.assigned_to = u.user_id
or c.requestor_id = u.user_id
where u.dept = 4
回答by Justin Rassier
If you are using T-SQL then it appears from previous posts that UNION removes duplicates. But if you are not, you could use distinct. This doesn't quite feel right to me either but it could get you the result you are looking for
如果您使用的是 T-SQL，那么从之前的帖子中可以看出 UNION 会删除重复项。但如果不是，则可以使用 distinct。这对我来说也不完全正确，但它可以让你得到你正在寻找的结果
SELECT DISTINCT *
FROM
(
select * from calls
left join users a on calls.assigned_to= a.user_id
where a.dept = 4
union
select * from calls
left join users r on calls.requestor_id= r.user_id
where r.dept = 4
)a
回答by Alberto Martinez
Since you are still getting duplicate using only UNIONI would check that:
由于您仍然只使用重复，UNION我会检查：
That they are exact duplicates. I mean, if you make a
SELECT DISTINCT * FROM (<your query>) AS subqueryyou do get fewer files?
That you don't have already the duplicates in the first part of the query (maybe generated by the left join). As I understand it
UNIONit will not add to the result set rows that are already on it, but it won't remove duplicates already present in the first data set.
它们是完全重复的。我的意思是，如果你做一个
SELECT DISTINCT * FROM (<your query>) AS subquery你得到的文件更少吗？
您在查询的第一部分中还没有重复项（可能由左连接生成）。据我了解，
UNION它不会添加到已经存在的结果集行中，但不会删除第一个数据集中已经存在的重复项。
回答by Joe Gurria Celimendiz
If you are using T-SQL you could use a temporary table in a stored procedure and update or insert the records of your query accordingly.
如果您使用 T-SQL，您可以在存储过程中使用临时表并相应地更新或插入查询记录。
