在SQL Server中查找重复行

Question

我有一个包含组织机构的 SQL Server 数据库，其中存在许多重复行。 我想运行一个 select 语句来获取所有这些重复行以及它们的数量，但也要返回与每个组织机构相关的 id 。类似于下面的语句：

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。有没有办法可以做到这一点？可能像这样：<\p>

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

原因是有一张单独的用户表与这些组织相关联，我想将它们统一起来（因此删除重复项，使用户链接到相同的组织而不是重复的组织）。但我想手动处理其中的一部分，以免出错，但仍需要一条语句返回所有重复组织的ID，以便我可以查看用户列表。

- xtine

Answer 1

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

- D'Arcy Rittich

---

这个查询有没有任何限制呢？比如说，如果记录数超过了一千万？ - Steam

@Steam 您是正确的：在包含数百万记录的大型数据库中，这个答案效率不高。建议使用Aykut提交的GroupBy/Having答案，可以更好地由数据库进行优化。唯一的例外是我建议使用Count(0)而不是Count(*)来简化事情。 - Mike Christian

@Mike - 为什么要使用Count(0)而不是Count(*)？ - KornMuffin

@KornMuffin 回想起来，我对Count()的评论是无效的。只有在您想要计算外部连接返回的非空结果时，使用Count()中的非空评估才有用。否则，请使用Count(*)。这里有一个很好的解释：[https://dev59.com/PXE85IYBdhLWcg3wkkfK]。 - Mike Christian

在on部分使用isnull()来处理可空列。 - Arif Ulusoy

Answer 2

您可以运行以下查询，并使用 max(id) 找到重复项，然后删除这些行。

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

但你需要运行这个查询几次。

- Aykut Akıncı

---

你必须确切地运行它 MAX( COUNT(*) ) - 1 次，这可能仍然是可行的。 - DerMike

嗨，有没有办法获取所有ID而不是最大ID，就像对于2我可以使用最大和最小值，但是对于超过2怎么办？@DerMike - Arijit Mukherjee

Answer 3

你可以这样做：

SELECT
    o.id, o.orgName, d.intCount
FROM (
     SELECT orgName, COUNT(*) as intCount
     FROM organizations
     GROUP BY orgName
     HAVING COUNT(*) > 1
) AS d
    INNER JOIN organizations o ON o.orgName = d.orgName

如果你想仅返回可以删除的记录（保留每种类型中的一条记录），你可以使用：

如果要返回可以删除的记录（仅保留每种类型中的一条记录），可以使用以下代码：

SELECT
    id, orgName
FROM (
     SELECT 
         orgName, id,
         ROW_NUMBER() OVER (PARTITION BY orgName ORDER BY id) AS intRow
     FROM organizations
) AS d
WHERE intRow != 1

编辑：SQL Server 2000没有ROW_NUMBER()函数。相反，您可以使用：

SELECT
    o.id, o.orgName, d.intCount
FROM (
     SELECT orgName, COUNT(*) as intCount, MIN(id) AS minId
     FROM organizations
     GROUP BY orgName
     HAVING COUNT(*) > 1
) AS d
    INNER JOIN organizations o ON o.orgName = d.orgName
WHERE d.minId != o.id

- Paul

---

第一条语句可以运行，但第二条似乎无法运行。 - xtine

SQL Server 似乎无法识别 row_number() 函数？ - xtine

啊...你有较早版本的 SQL Server 吗？我相信它是在 SQL Server 2005 中引入的。 - Paul

再次感谢，每次我需要做这件事时，我来到这里并且爱你。 - workabyte

Answer 4

你可以尝试这个，它最适合你了

 WITH CTE AS
    (
    SELECT *,RN=ROW_NUMBER() OVER (PARTITION BY orgName ORDER BY orgName DESC) FROM organizations 
    )
    select * from CTE where RN>1
    go

- code save

---

有没有办法以逗号分隔或不同的列中获取所有ID？ - Arijit Mukherjee

Answer 5

被标记为正确的解决方案对我没用，但我找到了这个很好用的答案：获取MySql中重复行的列表

SELECT n1.* 
FROM myTable n1
INNER JOIN myTable n2 
ON n2.repeatedCol = n1.repeatedCol
WHERE n1.id <> n2.id

- ecairol

---

你会在结果集中得到很多重复项，所以你也需要处理它们。 - Geeky Guy

如果id是数字，检查n1.id > n2.id将防止每对出现两次。 - starwed

Answer 6

如果您想删除重复项：

WITH CTE AS(
   SELECT orgName,id,
       RN = ROW_NUMBER()OVER(PARTITION BY orgName ORDER BY Id)
   FROM organizations
)
DELETE FROM CTE WHERE RN > 1

- akd

Answer 7

select * from [Employees]

查找重复记录 1）使用CTE（公共表达式）

with mycte
as
(
select Name,EmailId,ROW_NUMBER() over(partition by Name,EmailId order by id) as Duplicate from [Employees]
)
select * from mycte

2) 通过使用 GroupBy

select Name,EmailId,COUNT(name) as Duplicate from  [Employees] group by Name,EmailId 

- Debendra Dash

---

这是最快的解决方案，特别是当选择超过1000万行的数据时。谢谢。 - Fandango68

Answer 8

Select * from (Select orgName,id,
ROW_NUMBER() OVER(Partition By OrgName ORDER by id DESC) Rownum
From organizations )tbl Where Rownum>1

因此，具有rowum> 1的记录将是您的表中的重复记录。通过‘Partition by’首先对记录进行分组，然后通过给它们序列号来对其进行序列化。

因此，rownum> 1将是可删除的重复记录。

- Mike Clark

---

我喜欢这个查询，因为它允许你在内部选择子句中轻松添加更多的列。因此，如果你想从“组织”表返回其他列，你不必在那些列上执行“group by”操作。 - Gwasshoppa

Answer 9

select column_name, count(column_name)
from table_name
group by column_name
having count (column_name) > 1;

Src : https://dev59.com/OXVD5IYBdhLWcg3wL4gA#59242

- iCrazybest

---

这只适用于只有一列的表格。这很可能没有什么用处。 - Zach Smith

Answer 10

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

- user5336758