Implementation Method of Secondary Summary After Grouping (Processing Duplicate Record Query and Deletion of Tables)

--Handle duplicate records in table (query and delete)
/******************************************************************************************************************************************************
1, when Num, Name is the same, duplicate value records, no size relationship, keep only one
2, when Name is the same and ID has size relationship, keep one of the large or small records
Compiler: China style (Roy)

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

--1, used for querying duplicate processing records (if the column does not have size relationship)2000 using auto-increment column and temporary table to handle, SQL2005using the row_number function)

--> --> (Roy) Generate test data
　

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'),[Memo] nvarchar(
select 2,N'A',N'A2'),[Memo] nvarchar(
select 3,N'A',N'A3'),[Memo] nvarchar(
select 4,N'B',N'B1'),[Memo] nvarchar(
select 5,N'B',N'B2'
Go

--I. The record with the smallest ID where Name is the same (recommended to use1,2,3), method3in SQL05at the time, the efficiency is higher than1,2
Method1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)

Method2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID

Method3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)

Method4:
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

Method5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)

Method6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0

Method7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)

Method8:
select * from #T a where ID not > all(select ID from #T where Name=a.Name)

Method9(Note: ID is unique when available):
select * from #T a where ID in(select min(ID) from #T group by Name)

--SQL2005:

Method10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

Method11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1

Generate result:
/*
ID　　　　　　　　　 Name Memo
----------- ---- ----
1　　　　　　　　　　 A　　　 A1
4　　　　　　　　　　 B　　　 B1

(2 (Row affected)
*/

--II. Record with the largest ID that has the same Name, the opposite of min:
Method1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)

Method2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID

Method3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID

Method4:
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

Method5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)

Method6:
select * from #T a where (select count(1from #T where Name=a.Name and ID>a.ID)=0

Method7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

Method8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)

Method9(Note: ID is unique when available):
select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

Method10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

Method11:
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

Generate results2:
/*
ID　　　　　　　　　 Name Memo
----------- ---- ----
3　　　　　　　　　　 A　　　 A3
5　　　　　　　　　　 B　　　 B2

(2 (Row affected)
*/

--2, when deleting duplicate records and there is a size relationship, retain either the larger or smaller one

--> --> (Roy) Generate test data

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'),[Memo] nvarchar(
select 2,N'A',N'A2'),[Memo] nvarchar(
select 3,N'A',N'A3'),[Memo] nvarchar(
select 4,N'B',N'B1'),[Memo] nvarchar(
select 5,N'B',N'B2'
Go

--I. The record with the smallest ID where Name is the same (recommended to use1,2,3), retain the minimum one
Method1:
delete a from #T a where exists(select 1 from #T where Name=a.Name and ID<a.ID)

Method2:
delete a from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

Method3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

Method4(Note: ID is unique when available):
delete a from #T a where ID not in(select min(ID)from #T group by Name)

Method5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

Method6:
delete a from #T a where ID>(select top 1 ID from #T where Name=a.name order by ID)

Method7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)

select * from #T

Generate result:
/*
ID　　　　　　　　　 Name Memo
----------- ---- ----
1　　　　　　　　　　 A　　　 A1
4　　　　　　　　　　 B　　　 B1

(2 (Row affected)
*/

--II, Retain the maximum record with the same Name:

Method1:
delete a from #T a where exists(select 1 from #T where Name=a.Name and ID>a.ID)

Method2:
delete a from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

Method3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)

Method4(Note: ID is unique when available):
delete a from #T a where ID not in(select max(ID)from #T group by Name)

Method5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0

Method6:
delete a from #T a where ID>(select top 1 ID from #T where Name=a.name order by ID desc)

Method7:
delete a from #T a where ID<a(select ID from #T where Name=a.Name)

select * from #T
/*
ID　　　　　　　　　 Name Memo
----------- ---- ----
3　　　　　　　　　　 A　　　 A3
5　　　　　　　　　　 B　　　 B2

(2 (Row affected)
*/

--3When there is no size relationship in deleting duplicate records, handle duplicate values

--> --> (Roy) Generate test data
　
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

Method1:
if object_id('Tempdb..#') is not null
　　　 drop table #
Select distinct * into # from #T--Generate temporary table for result set excluding duplicate records#

truncate table #T--Clear table

insert #T select * from #　　　 --Insert temporary table # into table #T

--View Results
select * from #T

/*
Num　　　　　Name
----------- ----
1　　　　　　　　　　 A
2　　　　　　　　　　 B

(2 (Row affected)
*/

--Use method after re-executing test data2
Method2:

alter table #T add ID int identity--Add identifier 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)--Keep only one record
go
alter table #T drop column ID--Delete identifier column

--View Results
select * from #T

/*
Num　　　　　Name
----------- ----
1　　　　　　　　　　 A
2　　　　　　　　　　 B

(2 (Row affected)

*/

--Use method after re-executing test data3
Method3:
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 Roy_Cursor
fetch next from Roy_Cursor 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 Roy_Cursor
deallocate Roy_Cursor

--View Results
select * from #T
/*
Num　　　　　Name
----------- ----
1　　　　　　　　　　 A
2　　　　　　　　　　 B

(2 (Row affected)