SQL Server deletes the SQL statement of Repeated Records

There are two Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate name fields, other fields are not necessarily repeated or can be ignored.
1. For the first type of repetition, it is easier to solve.
Select distinct * From tablename
You can get the result set without repeated records.
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
The reason for this repetition is that the table design is not weekly. You can add a unique index column. 2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:
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)
The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing) 3. Some key fields are repeated and the record contains IDs. The first method deletes all duplicate records at a time (only records with the smallest ID in the repeat are retained ).
Delete from table where id not in (select Min (ID) from Table group by name) The second method deletes only one record with the largest duplicate ID at a time.

Delete from table where ID in (select max (ID) from Table group by name having count (*) & gt; 1)4. SQLProgramDeleteDeclare @ 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

You can also find the following methods:
Select * From user1 where [ID] Not in (select top 1
[ID] From user1 A where name = user1.name)
-- Delete and write it like this
Delete from user1 where [ID] Not in (select top 1 [ID] From user1 A where name = user1.name)
Or delete from user where id not in (select max (ID) from user where name = user. Name)
Delete [user] Where id not in (select max (ID) from [user] group by name having count (*)> 1)
Max or min depends on the actual situation.

Other methods:
---- A: retain the row with the largest ID and delete other rows.
-- Method 1
Delete [user] from [user] T
Inner join (Select name, max (ID) as ID from [user] group by name)
On T. Name = A. Name and T. ID <> A. ID
-- Method 2
Delete [user] from [user] T
Where exists (select * from [user] Where name = T. Name and ID> T. ID)

---- B: Keep the row with the smallest ID and delete other rows.
-- Method 1
Delete [user] from [user] T
Inner join (Select name, min (ID) as ID from [user] group by name)
On T. Name = A. Name and T. ID <> A. ID
-- Method 2
Delete [user] from [user] T
Where exists (select * from [user] Where name = T. Name and ID <t. ID)

---- C: delete all duplicate name rows without leaving one row
Delete [user] from [user] T
Inner join
(Select ID from [user] A where exists (select * from
[User] Where name = A. Name group by name having
Count (*)> 1) as B
On T. ID = B. ID There are two Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate name fields, other fields are not necessarily repeated or can be ignored.
1. For the first type of repetition, it is easier to solve.
Select distinct * From tablename
You can get the result set without repeated records.
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
The reason for this repetition is that the table design is not weekly. You can add a unique index column. 2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:
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)
The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing) 3. Some key fields are repeated and the record contains IDs. The first method deletes all duplicate records at a time (only records with the smallest ID in the repeat are retained ).
Delete from table where id not in (select Min (ID) from Table group by name) The second method deletes only one record with the largest duplicate ID at a time.

Delete from table where ID in (select max (ID) from table group by name having count (*) & gt; 1) 4. delete an SQL Program 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

You can also find the following methods:
Select * From user1 where [ID] Not in (select top 1
[ID] From user1 A where name = user1.name)
-- Delete and write it like this
Delete from user1 where [ID] Not in (select top 1 [ID] From user1 A where name = user1.name)
Or delete from user where id not in (select max (ID) from user where name = user. Name)
Delete [user] Where id not in (select max (ID) from [user] group by name having count (*)> 1)
Max or min depends on the actual situation.

Other methods:
---- A: retain the row with the largest ID and delete other rows.
-- Method 1
Delete [user] from [user] T
Inner join (Select name, max (ID) as ID from [user] group by name)
On T. Name = A. Name and T. ID <> A. ID
-- Method 2
Delete [user] from [user] T
Where exists (select * from [user] Where name = T. Name and ID> T. ID)

---- B: Keep the row with the smallest ID and delete other rows.
-- Method 1
Delete [user] from [user] T
Inner join (Select name, min (ID) as ID from [user] group by name)
On T. Name = A. Name and T. ID <> A. ID
-- Method 2
Delete [user] from [user] T
Where exists (select * from [user] Where name = T. Name and ID <t. ID)

---- C: delete all duplicate name rows without leaving one row
Delete [user] from [user] T
Inner join
(Select ID from [user] A where exists (select * from
[User] Where name = A. Name group by name having
Count (*)> 1) as B
On T. ID = B. ID