MSSQL sql server 2005/2008 row_number()函數應用之–刪除表中重

標籤：row_number

轉自:http://www.maomao365.com/?p=4942

**row_number函數在資料庫中的功能是為每一行 按照一定的規則產生一個編號，我們常常利用這一屬性，對錶進行分頁操作，下文我們將講述採用 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 [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()函數應用之–刪除表中重