过滤某几个重复字段

create table tb(姓名 varchar(10),性别 varchar(10),年龄 int)
insert into tb values('张三','男',20 )
insert into tb values('李四','男',30 )
insert into tb values('张三','男',40 )
go

 

--用OVER PARTITION BY 方式

WITH Liang AS
(
    SELECT *,  rnk=ROW_NUMBER() OVER(PARTITION BY 姓名,性别 ORDER BY 年龄) FROM tb
)
DELETE Liang WHERE rnk>1

 

--用临时表的方式

select distinct * into #Tmp from tb

drop table tb

select * into tb from #Tmp

drop table #Tmp