SQL Server 中处理重复数据：保留最新记录的两种方案

SQL Server 中处理重复数据：保留最新记录的两种方案

在数据库开发过程中，处理重复数据是一个常见的需求，特别是在需要保留最新记录的情况下。本文将介绍两种在SQL Server中实现这一目标的方法：使用ROW_NUMBER()函数和临时表方式。这两种方案都经过详细讲解，并附有具体代码示例，帮助开发者快速掌握相关技术。

准备测试数据

首先创建一个包含ID、OrderDate、ProductName以及可选的SequenceID的商品购买记录表Sales：

CREATE TABLE Sales
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATE NOT NULL,
    ProductName VARCHAR(100) NOT NULL, 
    SequenceID INT IDENTITY(1,1)
);
-- 订单日期增加当前日期默认值约束
ALTER TABLE Sales ADD DEFAULT (GETDATE()) FOR OrderDate;  

然后插入一些测试数据：

INSERT INTO Sales (OrderDate, ProductName)
VALUES 
    ('2023-04-01', '笔记本X1'), -- 示例商品A的最早购买日期
    ('2023-04-07', '智能手机Y7'),
    ('2023-04-15', '平板电脑Z3'),
    ('2023-04-09', '笔记本X1'), -- 商品A的第二次购买，较早日期
    ('2023-04-08', '智能手机Y7'), -- 商品B的第二次购买，较早日期
    ('2023-04-20', '平板电脑Z3'), -- 商品C的第二次购买，较晚日期
    ('2023-04-18', '笔记本X1'), -- 商品A的第三次购买，最新日期
    ('2023-04-22', '智能手机Y7 Pro'), -- 新产品，不同型号
    ('2023-04-25', '平板电脑Z3 Plus'), -- 新产品，不同型号
    ('2023-04-24 14:30:00', '笔记本X1'), -- 同日但较早时间的重复记录
    ('2023-04-24 15:45:00', '笔记本X1'); -- 同日但较晚时间的记录，应被视为最新  

方案一：使用ROW_NUMBER()函数删除重复项

ROW_NUMBER()函数是SQL Server中处理重复数据的强大工具之一，可以通过窗口函数来为每一组重复数据分配行号，然后保留每组数据中最新的一条记录。

SQL语句

假设有一个表Sales，包含ID、OrderDate、ProductName等字段，其中ID为主键，但ProductName和OrderDate上有重复数据，我们要保留每个产品的最新订单记录。

-- 查询不是最新的重复记录直接删除
WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY ProductName ORDER BY OrderDate DESC) AS RowNum
    FROM Sales
)
DELETE FROM CTE
WHERE RowNum > 1;
-- 数据库不操作直接查询每一行不重复的最新记录
WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY ProductName ORDER BY OrderDate DESC) AS RowNum
    FROM Sales
)
select * FROM CTE
WHERE RowNum = 1;  

执行效果

SQL说明

• PARTITION BY ProductName：按照ProductName对数据分组。
• ORDER BY OrderDate DESC：在每个分组内按OrderDate降序排序，确保最新记录排在首位。
• ROW_NUMBER()：为每组内的记录分配一个行号，最新的记录行号为1。
• 删除重复记录：在CTE中删除RowNum大于1的记录，即除了每个分组最新的一条记录外，其余视为重复并删除。
• 直接查询：针对CTE筛选RowNum等于1的记录。

方案二：使用临时表的方式

第二种方法是使用临时表来筛选并保留最新记录。具体步骤如下：

1. 创建临时表
2. 使用MERGE语句

SQL语句

INSERT INTO #TempSales
SELECT ID, OrderDate, ProductName
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ProductName ORDER BY OrderDate DESC) AS rn
    FROM Sales
) t
WHERE t.rn = 1;
select * from #TempSales; -- 直接查询就是去重后保留最新记录的查询数据
TRUNCATE TABLE Sales; -- 清空原表
-- 重新插入临时表的数据给Sales。适用数据量不是特别大的情况
INSERT INTO Sales
SELECT * FROM #TempSales;
DROP TABLE #TempSales; -- 删除临时表  

说明

该方案先通过临时表存储每个产品的最新记录，然后清空原表，并将临时表中的数据重新插入原表，最终达到保留最新记录的目的。直接查询临时表就是所需要的数据。