选择语句以查找某些字段的重复项

2022-01-31 00:00:00 sql tsql sql-server-2008 sql-server

您能帮我用 SQL 语句查找多个字段的重复项吗?

Can you help me with SQL statements to find duplicates on multiple fields?

例如，在伪代码中:

select count(field1,field2,field3) 
from table 
where the combination of field1, field2, field3 occurs multiple times

从上面的语句中如果有多次出现我想选择除了第一条之外的每条记录.

and from the above statement if there are multiple occurrences I would like to select every record except the first one.

推荐答案

要获取有多个记录的字段列表，可以使用..

To get the list of fields for which there are multiple records, you can use..

select field1,field2,field3, count(*)
  from table_name
  group by field1,field2,field3
  having count(*) > 1

查看此链接以获取有关如何删除行的更多信息.

Check this link for more information on how to delete the rows.

http://support.microsoft.com/kb/139444

应该有一个标准来决定如何定义第一行".在您使用上面链接中的方法之前.基于此，如果需要，您将需要使用 order by 子句和子查询.如果您可以发布一些示例数据，那真的很有帮助.

There should be a criterion for deciding how you define "first rows" before you use the approach in the link above. Based on that you'll need to use an order by clause and a sub query if needed. If you can post some sample data, it would really help.

相关文章