SQL-Example of processing table Repeated Records (query and deletion): query the last user logon log

-- 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.
Organized by: Roy)

Date: 2008.06.06
**************************************** **************************************** **************************************** ******************************/

-- 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)

--> (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

-- 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<A. ID)

Method 2:
Select a. * from # t a join (select Min (ID) ID, name from # T group by name) B on A. Name= B. NameAnd a. ID= B. ID

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

Method 4:
Select a. * from # t a join # t B on A. Name= B. NameAnd a. ID>= B. ID group by A. ID, A. Name, A. 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<A. ID)= 0

Method 7:
Select * from # t a where ID= (SelectTop 1 ID from # t where name= A. NameOrder 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 a. * from # t a join # t B on A. Name = B. Name and A. ID<= B. ID group by A. ID, A. Name, A. Memo having count (1)= 1

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

Method 6:
Select * from # t a where (select count (1) from # t where name= A. NameAnd 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!<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 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<A. ID)

Method 2:
Delete A from # t a left join (select Min (ID) ID, name from # T group by name) B on A. Name= B. NameAnd a. ID= B. IDWhere 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. NameAnd 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)

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<Any(Select ID from # t where name= A. Name)

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)

*/

-- run the test data again and use method 3
method 3:
declare roy_cursor cursor local for
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 maid
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 roy_cursor into @ con, @ num, @ name
end
close maid
deallocate maid

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

(2 rows affected)
*/