SQL Server：根据单个列中的值在表中查找重复项 [英] SQL Server : find duplicates in a table based on values in a single column

问题描述

我有一个包含以下字段和示例数据的SQL Server表：

  ID employeename 
 1 Jane 
 2 Peter 
 3 David 
 4 Jane 
 5 Peter 
 6 Jane 
  

ID 列每行都有唯一的值。

employeename 列有重复。

我想要能够根据 employeename 列，并列出由逗号分隔的副本的 ID 。

输出预期以上样本数据：

  employeename ID 
简1,4,6 
彼得2,5 
  

表中还有其他列，我不想考虑这个查询。 >

感谢您的帮助！

解决方案

 选择
 employeename，
 ID = STUFF（（SELECT'，'+ CAST（e 2. [ID] AS VARCHAR（10））
 FROM emp e2 
 WHERE e2.employeename = e1.employeename 
对于XML PATH（''）
），1,1 ，''）
 FROM emp e1 
 GROUP BY employeename有COUNT（*）> 1 
  

SQL Fiddler

I have a SQL Server table with the following fields and sample data:

ID   employeename
1    Jane
2    Peter
3    David
4    Jane
5    Peter
6    Jane

The ID column has unique values for each row.

The employeename column has duplicates.

I want to be able to find duplicates based on the employeename column and list the IDs of the duplicates next to them separated by commas.

Output expected for above sample data:

employeename   IDs
Jane           1,4,6
Peter          2,5

There are other columns in the table that I do no want to consider for this query.

Thanks for all your help!

解决方案

select
 employeename,
 IDs = STUFF((SELECT ','+ CAST(e2.[ID] AS VARCHAR(10)) 
  FROM emp e2
  WHERE e2.employeename = e1.employeename
  For XML PATH('')
 ),1,1,'')
FROM emp e1
GROUP BY employeename having COUNT(*) > 1

SQL Fiddler

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