要求用一条sql语句删除表内的重复数据

1.某字段重复

create table testB (id int identity(1,1),name varchar(50))

insert into testB(name)
select 'aa'
union all
select 'aa'
union all
select 'bb'
union all
select 'cc'
union all
select 'cc'

--select * from testb

要求最后表内结果

id name
2 aa
3 bb
5 cc

2.某些行完全重复

create table TestA(id int ,name varchar(50))

insert into TestA (id,name)
select '1','aa'
union all
select '1','aa'
union all
select '2','cc'
union all
select '2','dd'
union all
select '2','dd'
union all
select '3','dd'

--select * from testa

 

要求最后表内结果

id name
1 aa
2 cc
2 dd
3 dd

 

 

方案

-----------------------------------------

1. delete from dbo.testB where  id not in(select MAX(id) from dbo.testB group by name)

2.(也可用临时表)

WITH b as (
SELECT ROW_NUMBER() OVER ( PARTITION BY id,name ORDER BY id) AS rn,* FROM TestA)
delete from b where rn > 1