从 sql union 中删除重复项

我正在使用联合(对或错)对几个表执行一些基本的 sql

但我需要移除复制品，有什么办法吗？

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
159287 次浏览
小开
最佳答案

Union will remove duplicates. Union All does not.

小开

Using UNION automatically removes duplicate rows unless you specify UNION ALL: http://msdn.microsoft.com/en-us/library/ms180026(SQL.90).aspx

小开

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
小开

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

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
小开

Since you are still getting duplicate using only UNION I would check that:

  • That they are exact duplicates. I mean, if you make a

    SELECT DISTINCT * FROM (<your query>) AS subquery

    you 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 UNION it 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.

小开

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.


提交答案