在SQL Server中查找重复的行 [英] Finding duplicate rows in SQL Server

问题描述

我有一个组织的SQL Server数据库，并且有很多重复的行.我想运行一条select语句来获取所有这些信息和重复数量，还要返回与每个组织相关联的ID.

I have a SQL Server database of organizations, and there are many duplicate rows. I want to run a select statement to grab all of these and the amount of dupes, but also return the ids that are associated with each organization.

这样的语句:

SELECT     orgName, COUNT(*) AS dupes  
FROM         organizations  
GROUP BY orgName  
HAVING      (COUNT(*) > 1)

将返回类似的内容

orgName        | dupes  
ABC Corp       | 7  
Foo Federation | 5  
Widget Company | 2 

但是我也想获取它们的ID.有什么办法吗?也许像

But I'd also like to grab the IDs of them. Is there any way to do this? Maybe like a

orgName        | dupeCount | id  
ABC Corp       | 1         | 34  
ABC Corp       | 2         | 5  
...  
Widget Company | 1         | 10  
Widget Company | 2         | 2  

原因是还有一个单独的链接到这些组织的用户表，我想统一它们(因此删除重复对象，以便用户链接到同一组织而不是重复组织).但是我想手动退出，所以我不会搞砸任何东西，但是我仍然需要一条语句来返回所有重复组织的ID，这样我才能遍历用户列表.

The reason being that there is also a separate table of users that link to these organizations, and I would like to unify them (therefore remove dupes so the users link to the same organization instead of dupe orgs). But I would like part manually so I don't screw anything up, but I would still need a statement returning the IDs of all the dupe orgs so I can go through the list of users.

推荐答案

select o.orgName, oc.dupeCount, o.id
from organizations o
inner join (
    SELECT orgName, COUNT(*) AS dupeCount
    FROM organizations
    GROUP BY orgName
    HAVING COUNT(*) > 1
) oc on o.orgName = oc.orgName

这篇关于在SQL Server中查找重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！