MSSQL SQL Server 2005/2008 row_number () function application – Delete table in heavy

Transferred from: http://www.maomao365.com/?p=4942

The function of the **row_number function in the database is to generate a number for each row according to a certain rule, we often use this property, the table paging operation, the following we will describe the use of the Row_number function to delete the table repeating data row * *

/*建表*/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 [A2] from (select row_number() over (Partition By keyId order by keyId) as keyId2,* from A ) as [A2]where [A2].keyId2  >1 /*/*删除 所有列都重复数据 中的另外几条*/delete [A2] from (select row_number() over (Partition By keyId,info order by keyId) as keyId2,* from A ) as [A2]where [A2].keyId2  >1  *//*展示删除后的数据*/select * from A gotruncate table A drop table A go

MSSQL SQL Server 2005/2008 row_number () function application – Delete table in heavy