在SQL Server 2000的时候我们删除重复记录比较常用的方法就是利用自动增长ID列,在SQL Server 2005里我们可以利用新的特性来实现这个功能。几个实用的特性如下:
1、ROW_NUMBER,它的作用就是用来生成行号,默认是从1开始。
2:公用表表达式(CTE),我这里并不会利用它的递归,而是用它来简化嵌套查询及对表自身引用功能。CTE的语法如下:
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )
示例:
USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO
3:PARTITION BY,分区函数。和聚合函数不同的地方在于它能返回一个分组中的多条记录,聚合函数一般只有一条反映统计值的记录,partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组 。
整合上面所说的3个实用特性,在SQL Server 2005中删除重复记录的语句如下:
WITH t_Temp AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY name ORDER BY name,CreateDate desc ) AS RowNumber, *
FROM t_StaffInfo
)
delete FROM t_Temp --删除重复。查询重复用 select * FROM t_Temp
WHERE RowNumber > 1