SQL statement for deleting duplicate data

SQL statement for deleting duplicate data
Method 1

Assume that the duplicate fields are name and address. You must obtain the unique result set of the two fields.
Select identity (int, 1, 1) as autoid, * into # tmp from tablename
Select min (autoid) as autoid into # tmp2 from # tmp group by name, autoid
Select * from # tmp where autoid in (select autoid from # tmp2)

Method 2
Declare @ max integer, @ id integer
Declare cur_rows cursor local for select Main field, count (*) from table name group by main field having count (*)> 1
Open cur_rows
Fetch cur_rows into @ id, @ max
While @ fetch_status = 0
Begin
Select @ max = @ max-1
Set rowcount @ max
Delete from table name where primary field = @ id
Fetch cur_rows into @ id, @ max
End
Close cur_rows
Set rowcount 0

Method 3

If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:
Select distinct * into # tmp from tablename
Drop table tablename
Select * into tablename from # tmp
Drop table # tmp

Instance

Vieworder ID, Card Punching machine, consumption type, time, price
1 a1 machine 1 breakfast 07:09:23. 000 2
2 a1 machine 1 breakfast 07:10:13. 000 2
3 a1 machine 1 breakfast 07:10:19. 000 2
4 a1 1 Machine lunch 12:02:10. 000 5
5 a2 machine 1 lunch 12:11:10. 000 5
6 a2 machine 1 lunch 12:12:10. 000 5

Code

Delete from Table
Where exists (select * from table where consumption type = a. Consumption Type and time> = dateadd (minute,-2, a. Time) and time <a. Time)

Use the select statement to view the data to be deleted before deletion.
Select *
From Table
Where exists (select * from table where consumption type = a. Consumption Type and time> = dateadd (minute,-2, a. Time) and time <a. Time)