MSSQL sql server 2005/2008 row_number()函数应用之–删除表中重
/*建表*/ create table A(keyId int,info varchar(200))go
/*生成数据*/
insert into A(keyId,info)values
(1,'a'),(2,'b'),(3,'C'),(4,'d'),(5,'e'),
(1,'a'),(21,'b1'),(31,'C1'),(4,'d'),(51,'猫猫小屋'),
(1,'a'),(6,'b1'),(7,'C1'),(4,'d000'),(10,'maomao365.com')
go
/*删除 keyId重复数据 中的另外几条*/
delete from
(select row_number() over (Partition By keyId order by keyId) as keyId2,* from A ) as
where .keyId2>1
/*
/*删除 所有列都重复数据 中的另外几条*/
delete from
(select row_number() over (Partition By keyId,info order by keyId) as keyId2,* from A ) as
where .keyId2>1
*/
/*展示删除后的数据*/
select * from A
go
truncate table A
drop table A
go
页:
[1]