SQL删除重复记录

CREATE TABLE [dbo].[Test](
 [id] [int] IDENTITY(1,1) primary key NOT NULL,
 [name] [varchar](20) NULL,
 [age] [varchar](4) NULL
)
go
insert into Test(name,age)
values('connor','22'),('connor','23'),('connor','23'),('summerleoo','22'),('summerleoo','22'),('tina','24'),('tina','22')
go
select * from Test

/*
--删除id 最小的重复记录，
--单字段 [name]
delete from Test where id in(
select id from
(select Test.* ,min(Test.ID)over(partition by Test.Name) as MinID from  Test  inner join (select name from Test group by name having count(*)>1 )b on Test.name = b.name )T where id = MinID
)
--多字段 [name,age]
delete from Test where id in(
select id from
(select Test.* ,min(Test.ID)over(partition by Test.Name) as MinID from  Test  inner join (select name,age from Test group by name,age having count(*)>1 )b on Test.name = b.name and Test.age = b.age )T where id = MinID
)

*/

 

转载于:https://www.cnblogs.com/justconnor/archive/2012/03/14/2395662.html