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