在 SQL Server 中查找重复行

2022-01-10 00:00:00 sql duplicates 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

相关文章