sql - 选择语句以查找某些字段的重复项(Select statement to find duplicates on certain fields)

Question

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

(你能帮我用SQL语句来查找多个字段的重复项吗？)

For example, in pseudo code:

(例如，在伪代码中：)

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 .

(并且从上面的陈述中， 如果有多次出现，我想选择除第一个以外的每个记录 。)

  ask by JOE SKEET translate from so

Answer 1

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

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

Edit : As the other users mentioned, 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.

(基于此，您需要使用order by子句和子查询（如果需要）。)

If you can post some sample data, it would really help.

(如果您可以发布一些示例数据，那将非常有用。)