SQL Server:根据单列中的值查找表中的重复项

2022-01-10 00:00:00 sql duplicates sql-server duplicate-data

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

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

ID 列的每一行都有唯一值.

The ID column has unique values for each row.

employeename 列有重复项.

我希望能够根据 employeename 列找到重复项，并在它们旁边列出重复项的 ID，用逗号分隔.

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.

感谢您的帮助！

推荐答案

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

相关文章