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

)

*/