The SQL read information is not repeated.

-- Process duplicate table records (query and delete)
/*************************************** **************************************** **************************************** *******************************
1. Repeat records with the same num and name. Only one record is retained if there is no size relationship.
2. if the name is the same and the ID has a size relationship, keep one of the records large or small.

**************************************** **************************************** **************************************** ******************************/

-- 1. Used to query repeat processing records (if the column has no size relationship, 2000 use the auto-incrementing column and temporary table for processing, and sql2005 uses the row_number function for processing)

-->

If not object_id ('tempdb .. # t') is null
Drop table # T
Go
Create Table # T ([ID] int, [name] nvarchar (1), [memo] nvarchar (2 ))
Insert # T
Select 1, n 'A', N 'a1' Union all
Select 2, N 'A', N 'a2 'Union all
Select 3, N 'A', N 'a3 'Union all
Select 4, N 'B', N 'b1 'Union all
Select 5, N 'B', N 'b2'
Go

-- Records with the smallest IDs of I and name (1, 2, 3 are recommended). Method 3 is more efficient than 1 and 2 in sql05.
Method 1:
Select * from # t a where not exists (select 1 from # t where name = A. Name and ID
Method 2:
Select. * From # t a join (select Min (ID) ID, name from # T group by name) B on. name = B. name and. id = B. ID

Method 3:
Select * from # t a where id = (select Min (ID) from # t where name = A. Name)

Method 4:
Select. * From # t a join # t B on. name = B. name and. id> = B. id group by. ID,. name,. memo having count (1) = 1

Method 5:
Select * from # t a group by ID, name, memo having id = (select Min (ID) from # t where name = A. Name)

Method 6:
Select * from # t a where (select count (1) from # t where name = A. Name and ID
Method 7:
Select * from # t a where id = (select top 1 ID from # t where name = A. Name order by ID)

Method 8:
Select * from # t a where Id!> All (select ID from # t where name = A. Name)

Method 9 (Note: It is available when ID is unique ):
Select * from # t a where ID in (select Min (ID) from # T group by name)

-- Sql2005:

Method 10:
Select ID, name, memo from (select *, min (ID) over (partition by name) as minid from # t a) t where id = minid

Method 11:

Select ID, name, memo from (select *, row_number () over (partition by name order by ID) as minid from # t a) t where minid = 1

Result:
/*
ID name memo
-------------------
1 A A1
4 B B1

(2 rows affected)
*/

-- II. records with the same ID and maximum name, opposite to Min:
Method 1:
Select * from # t a where not exists (select 1 from # t where name = A. Name and ID> A. ID)

Method 2:
Select. * From # t a join (select max (ID) ID, name from # T group by name) B on. name = B. name and. id = B. id order by ID

Method 3:
Select * from # t a where id = (select max (ID) from # t where name = A. Name) order by ID

Method 4:
Select. * From # t a join # t B on. name = B. name and. ID <= B. id group by. ID,. name,. memo having count (1) = 1

Method 5:
Select * from # t a group by ID, name, memo having id = (select max (ID) from # t where name = A. Name)

Method 6:
Select * from # t a where (select count (1) from # t where name = A. Name and ID> A. ID) = 0

Method 7:
Select * from # t a where id = (select top 1 ID from # t where name = A. Name order by id desc)

Method 8:
Select * from # t a where ID!
Method 9 (Note: It is available when ID is unique ):
Select * from # t a where ID in (select max (ID) from # T group by name)

-- Sql2005:

Method 10:
Select ID, name, memo from (select *, max (ID) over (partition by name) as minid from # t a) t where id = minid

Method 11:
Select ID, name, memo from (select *, row_number () over (partition by name order by id desc) as minid from # t a) t where minid = 1

Result 2:
/*
ID name memo
-------------------
3 A A3
5 B B2

(2 rows affected)
*/

-- 2. When deleting duplicate records with a size relationship, keep one of the records large or small.

--> (Roy) generate a sequence number

If not object_id ('tempdb .. # t') is null
Drop table # T
Go
Create Table # T ([ID] int, [name] nvarchar (1), [memo] nvarchar (2 ))
Insert # T
Select 1, n 'A', N 'a1' Union all
Select 2, N 'A', N 'a2 'Union all
Select 3, N 'A', N 'a3 'Union all
Select 4, N 'B', N 'b1 'Union all
Select 5, N 'B', N 'b2'
Go

-- Record with the minimum ID for I and name (1, 2, 3 is recommended), and the minimum record is retained
Method 1:
Delete A from # t a where exists (select 1 from # t where name = A. Name and ID
Method 2:
Delete A from # t a left join (select Min (ID) ID, name from # T group by name) B on. name = B. name and. id = B. ID where B. ID is null

Method 3:
Delete A from # t a where id not in (select Min (ID) from # t where name = A. Name)

Method 4 (Note: It is available when ID is unique ):
Delete A from # t a where id not in (select Min (ID) from # T group by name)

Method 5:
Delete A from # t a where (select count (1) from # t where name = A. Name and ID 0

Method 6:
Delete A from # t a where ID <> (select top 1 ID from # t where name = A. Name order by ID)

Method 7:
Delete A from # t a where ID> Any (select ID from # t where name = A. Name)

Select * from # T

Result:
/*
ID name memo
-------------------
1 A A1
4 B B1

(2 rows affected)
*/

-- II. Keep the largest record with the same ID as name:

Method 1:
Delete A from # t a where exists (select 1 from # t where name = A. Name and ID> A. ID)

Method 2:
Delete A from # t a left join (select max (ID) ID, name from # T group by name) B on. name = B. name and. id = B. ID where B. ID is null

Method 3:
Delete A from # t a where id not in (select max (ID) from # t where name = A. Name)

Method 4 (Note: It is available when ID is unique ):
Delete A from # t a where id not in (select max (ID) from # T group by name)

Method 5:
Delete A from # t a where (select count (1) from # t where name = A. Name and ID> A. ID)> 0

Method 6:
Delete A from # t a where ID <> (select top 1 ID from # t where name = A. Name order by id desc)

Method 7:
Delete A from # t a where ID

Select * from # T
/*
ID name memo
-------------------
3 A A3
5 B B2

(2 rows affected)
*/

-- 3. Process duplicate values when deleting duplicate records with no size relationship

--> (Roy) generate a sequence number

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 num = @ num and name = @ name
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)
*/