When the SQL statement deletes duplicate records and there is no size relationship between them, it processes repeated values.

When the SQL statement deletes duplicate records and there is no size relationship between them, it processes repeated values.

When the SQL statement deletes duplicate records and there is no size relationship between them, it processes repeated values.

When the SQL statement deletes duplicate records and there is no size relationship between them, it processes repeated values.

--> (Roy) generation

If not object_id ('tempdb .. # t') is null
Drop table # t
Go
Create table # t ([num] int, [name] nvarchar (1 ))
Insert # t
Select 1, n 'A' union all
Select 1, n 'A' union all
Select 1, n 'A' union all
Select 2, n 'B' union all
Select 2, n 'B'
Go

Method 1:

If object_id ('tempdb .. # ') is not null
Drop table #
Select distinct * into # from # t -- exclude duplicate record result set to generate a temporary table #

Truncate table # t -- clear the table

Insert # t select * from # -- insert temporary table # into Table # t

-- View results
Select * from # t

/*
Num name
---------------
1
2 B

(2 rows affected)
*/

-- Method 2 after re-executing the test data
Method 2:

Alter table # t add id int identity -- add id column
Go
Delete a from # t a where exists (select 1 from # t where num = a. num and name = a. name and id> a. id) -- only one record is retained.
Go
Alter table # t drop column id -- delete the id column

-- View results
Select * from # t

/*
Num name
---------------
1
2 B

(2 rows affected)

*/

-- Method 3 after re-executing the test data
Method 3:

Declare roy_cursor cursor local
Select count (1)-1, num, name from # t group by num, name having count (1)> 1
Declare @ con int, @ num int, @ name nvarchar (1)
Open roy_cursor
Fetch next from Maid into @ con, @ num, @ name
While @ fetch_status = 0
Begin
Set rowcount @ con;
Delete # t where and
Set rowcount 0;
Fetch next from Maid into @ con, @ num, @ name
End
Close roy_cursor
Deallocate roy_cursor

-- View results
Select * from # t
/*
Num name
---------------
1
2 B

(2 rows affected)
*/
// Exploitation

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

// Use the Function

Select distinct * into # tmp from tablename
Drop table tablename
Select * into tablename from # tmp
Drop table # tmp