随笔 - 9  文章 - 2  评论 - 51 

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
)

*/

 

posted on 2012-03-14 12:02  justconnor  阅读(173)  评论(0编辑  收藏