做数据处理的人早晚都会碰到这种事:报表上某个订单金额突然翻了几倍,查下去发现订单表里同一个单号出现了好几遍;客户对账时对方返回来一堆看起来一模一样的记录;又或者导入接口因为重试机制把同一条数据插了多次。在MS SQL Server里,统计与汇总重复记录几乎是每个业务库都绕不过去的场景。这篇文章不聊教科书理论,直接讲我实际处理这些需求时总结下来的经验:怎么准确统计重复、怎么按业务维度汇总、怎么安全地清理重复记录,以及在大表上怎么避免SQL直接跑崩。内容适合刚接触SQL的开发、数据分析师,以及正在为重复数据头疼的运维和兼职DBA。1. 重复记录的两类形态:先分清整行重复和业务键重复很多人拿到需求就急着写GROUP BY删数据,实际上一半的问题出在你根本没搞清楚什么算重复。我处理过的重复数据大致可以分成两类,处理方式完全不同。1.1 完全重复:整行所有字段都一模一样这类重复最直观,通常是程序bug、接口重试、或者手工导数据时重复粘贴导致的。判断方法也很简单:把表的每一列都放进GROUP BY,统计数量大于1的就是重复行。SELECT 所有业务字段, COUNT(*) AS 重复次数 FROM 订单表 GROUP BY 所有业务字段 HAVING COUNT(*) 1;但这里有个隐蔽的坑:如果表里带自增主键OrderID,那所有字段就得把主键排除掉,否则每一行都是唯一的。很多时候主键唯一,业务字段却完全一致,用GROUP BY *没法用,必须显式列出业务列。列很多的时候,一行一行敲字段非常痛苦。我早期偷懒用过CHECKSUM(*)来快速判断整行是否重复:SELECT CHECKSUM(*), COUNT(*) AS 重复次数 FROM 订单表 GROUP BY CHECKSUM(*) HAVING COUNT(*) 1;实测下来极快,但CHECKSUM存在哈希碰撞的可能,两条完全不同的记录理论上可能算出同一个校验值。所以我把它定位成快速筛查工具,最终确认重复一定用HASHBYTES或者直接把关键列拼成字符串比较。SQL Server 2017以上推荐用HASHBYTES(SHA2_256, 列拼接),碰撞概率可以忽略。1.2 业务键重复:核心字段重复,其他字段不同实际业务里更常见的是这一类:同一个订单号、同一个客户编号、同一个产品编号,但是下单时间、备注、操作员、甚至金额不同。为什么会这样?可能是业务系统没有唯一约束;可能是历史数据从多个系统迁移时撞了单号;也可能是有人手工补录时复制了单号。判断这类重复,重点在于找出业务上应该唯一的一组字段。比如订单表里OrderNo应该唯一,那它就是业务键;再比如库存表里仓库 产品 批次应该唯一,那就是三字段组成的联合业务键。SELECT OrderNo, COUNT(*) AS 重复次数 FROM 订单表 GROUP BY OrderNo HAVING COUNT(*) 1;1.3 判断重复字段的三个原则这里分享我踩过坑之后总结出来的选字段原则:第一,必须拿业务上能说清楚的字段作为业务键,不要自己拍脑袋。比如我认为客户名金额一样就重复,这种最好先和业务方确认,因为两个不同客户完全可能同名、同金额。第二,字段越少越容易误判,字段越多越可能漏判。先用一到两个高区分度字段(如单号、流水号)初筛,再用辅助字段二次确认。第三,注意NULL。GROUP BY里NULL会被当成一组,比如CustomerID为空的记录会被统计在一起;但某些业务场景下,NULL并不代表重复。统计前可以先过滤或者把NULL替换成占位符。提示:做重复统计前,先问自己一句这份数据的业务键是什么。业务键没定清楚,后面的所有统计、汇总、删除都是空中楼阁。2. 统计重复的三条路线:GROUP BY、窗口函数、自连接怎么选统计重复的方法远不止一种,我平时最常用的有三条路线,分别对应不同场景。2.1 GROUP BY HAVING COUNT(*) 1:最直观的统计方式这是大家最熟悉的写法,适合快速统计重复范围和规模:SELECT OrderNo, COUNT(*) AS 重复记录数, COUNT(DISTINCT Amount) AS 金额种类数, MIN(OrderID) AS 最小明细ID, MAX(OrderID) AS 最大明细ID FROM 订单表 GROUP BY OrderNo HAVING COUNT(*) 1 ORDER BY COUNT(*) DESC;COUNT(DISTINCT Amount)这个字段很有用,如果重复的这些记录金额不一致,说明同单号不同内容,可能不是简单的重复插入,而是数据本身存在版本差异,需要人工介入。这样一步就能把纯重复和疑似冲突分开。统计行数是SQL最基础的聚合操作,COUNT(*)统计包含NULL在内的所有行,COUNT(列名)只统计该列非NULL的行,这在核对数据量时经常用到,别搞混了。2.2 窗口函数:把重复明细和编号一次都查出来GROUP BY适合只想知道哪些键重复了,但如果还想看到每条明细是第几条、保留哪一条,窗口函数更方便。ROW_NUMBER()在SQL Server里的语法非常稳定,也支持分区排序:SELECT OrderID, OrderNo, Amount, CreatedAt, ROW_NUMBER() OVER(PARTITION BY OrderNo ORDER BY CreatedAt DESC) AS rn, COUNT(*) OVER(PARTITION BY OrderNo) AS 重复总次数 FROM 订单表 ORDER BY OrderNo, rn;这条SQL的结果里,rn 1代表在每个OrderNo分组内按CreatedAt倒序后的第一条,也就是最新那条;重复总次数表示这个单号总共重复了几次。后面做删除、保留、归档,基本都是围绕这个rn字段展开。窗口函数的优势是:不需要提前分组聚合,每行都能带着自己的重复序号,排查时非常直观,而且一次扫描就能完成,性能在实际执行计划中往往优于多次GROUP BY。2.3 自连接:处理无法用单一字段分组的复杂场景有些重复不体现在某个字段完全相同,而体现在同一客户在很短时间内重复提交。这时候GROUP BY和窗口函数都不太好处理,我会用自连接加范围条件:SELECT a.OrderID AS 当前订单, b.OrderID AS 相似订单, a.CustomerID, a.Amount FROM 订单表 a INNER JOIN 订单表 b ON a.CustomerID b.CustomerID AND a.Amount b.Amount AND a.OrderID b.OrderID -- 只保留单方向组合,避免重复 AND DATEDIFF(MINUTE, a.CreatedAt, b.CreatedAt) 5;这里a.OrderID b.OrderID是自连接的精髓,不加上会得到镜像重复结果,一条相似记录产生两行,加上以后每组相似对只出现一次。自连接性能通常不如前两种,数据量大时要谨慎,建议先加WHERE条件缩小范围,再配合索引。2.4 三条路线的对比和选型方法适合场景优点缺点GROUP BY HAVING统计重复键的分布、判断重复规模语法简单、聚合效率高看不到重复明细,需要再关联查询窗口函数 ROW_NUMBER删除/保留、逐行标记重复序号每条明细带序号,后续处理直接复用结果集较大,需要临时表或CTE配合自连接相似记录判断、范围重复能表达字段完全不等的复杂关系性能消耗大,容易产出笛卡尔积实际项目里,我大概九成场景用前两种,自连接只在做相似订单识别这类偏数据挖掘的需求时才上。3. 汇总重复记录:从数清重复到合并成一条统计重复只是第一步,很多需求真正要的是汇总:把这些重复记录按某种规则合并成一个结果。3.1 按业务键汇总指标:SUM、AVG、MAX的组合应用比如同一个订单号出现了3条记录,每条金额都是100,业务方想知道按单号合并后应该记多少金额。最常见的错误是直接SUM(Amount)得到300,但300是重复造成的虚高,不是真实金额。正确的做法是先确认重复记录中哪一条是真身,或者用MAX/AVG消除重复带来的虚增:SELECT OrderNo, MIN(Amount) AS 最小金额, MAX(Amount) AS 最大金额, AVG(Amount) AS 平均金额, COUNT(*) AS 重复条数 FROM 订单表 GROUP BY OrderNo HAVING COUNT(*) 1;对比MIN和MAX就能看出重复记录之间有没有金额偏差。如果两者一致,说明只是同一数据被重复插入了,汇总时取其中任意一条;如果不一致,说明重复的数据本身有变化,必须走人工核对流程,不能用AVG糊弄过去。3.2 多行合并成一行:STRING_AGG 拼接字段还有一种汇总是把重复行的某些字段拼起来,形成一行摘要。比如重复的订单记录里各有不同的备注、不同的附件说明,业务方希望看到这一个单号下到底有哪些备注。SQL Server 2017以上可以直接用STRING_AGG:SELECT OrderNo, COUNT(*) AS 重复条数, STRING_AGG(Remark, ; ) AS 备注汇总, STRING_AGG(CONVERT(VARCHAR(20), OrderID), ,) AS 明细ID列表 FROM 订单表 GROUP BY OrderNo HAVING COUNT(*) 1;如果是老版本SQL Server(2017以下),没有STRING_AGG,可以用FOR XML PATH代替。这个老办法我还在不少老客户系统里用:SELECT OrderNo, STUFF(( SELECT ; Remark FROM 订单表 AS b WHERE b.OrderNo a.OrderNo FOR XML PATH() ), 1, 2, ) AS 备注汇总 FROM 订单表 AS a GROUP BY OrderNo HAVING COUNT(*) 1;STUFF加FOR XML PATH是SQL Server老版本拼接字符串的经典组合,把子查询里每行前拼上分隔符,再用STUFF去掉开头两个字符。缺点是用在数据量大的表上会慢,我一般会先缩小到重复候选集再做拼接。3.3 汇总统计时最容易踩的三个坑第一个坑是SUM被重复数据凭空放大。我在处理一个进销存系统的采购入库单时,上游传了两次,结果月末库存金额汇总直接多出37%。这是重复没识别就直接汇总的典型后果。第二个坑是忽略了NULL参与聚合。SUM遇到NULL会跳过,COUNT(*)却会计数,同一个字段、同一条记录,用两种统计方式得到的行数不一样,核对的时候容易以为的自己SQL写错了。第三个坑是对重复权重没概念。如果需要按重复次数加权计算某种指标,必须显式写出权重。比如统计每个客户的投诉标签分布,同一条投诉重复了3次,类别分布就失真了。这种情况要先用去重逻辑把基础数据集生成好,再跑聚合,不要直接基于原始表做汇总。注意:如果汇总结果要用于报表或者财务对账,务必把是否排除重复这个前提写清楚,最好在汇总SQL里保留COUNT(*)字段,给下游留一个校验口。4. 从统计到治理:保留哪一条重复记录的SQL策略统计和汇总做完,绝大多数需求都指向最后一步:把重复数据清掉,只留一条。这一步操作风险极高,稍不注意就删掉了不该删的数据。4.1 用CTE ROW_NUMBER保留固定规则的一条最简单的场景:每个业务键只保留最新一条,其他标记删除。SQL Server的CTE支持直接对底表进行DELETE操作,这是我日常清理最常用的写法:WITH ranked AS ( SELECT OrderID, OrderNo, Amount, CreatedAt, ROW_NUMBER() OVER(PARTITION BY OrderNo ORDER BY CreatedAt DESC) AS rn FROM 订单表 ) DELETE FROM ranked WHERE rn 1;可以看到,ROW_NUMBER按CreatedAt倒序编号,rn 1是最新一条,删除rn 1就是保留最新。如果业务规则是保留最早一条,把ORDER BY CreatedAt DESC改成ASC;如果规则是保留金额非空、ID最大的一条,就改成ORDER BY CASE WHEN Amount IS NULL THEN 1 ELSE 0 END, OrderID DESC。排序规则实际上就是保留优先级,想清楚再写。4.2 保留策略不唯一时的处理:优先级排序实际场景里保留哪一条经常没有绝对标准,这时候我会把规则转成分步骤的优先级。比如:优先保留金额非空且状态为已确认的记录,其次保留最新记录:WITH ranked AS ( SELECT OrderID, OrderNo, Amount, Status, CreatedAt, ROW_NUMBER() OVER( PARTITION BY OrderNo ORDER BY CASE WHEN Amount IS NOT NULL AND Status 已确认 THEN 0 WHEN Amount IS NOT NULL THEN 1 ELSE 2 END, CreatedAt DESC ) AS rn FROM 订单表 ) DELETE FROM ranked WHERE rn 1;这样处理后,rn 1一定是业务状态最完整的一条,而不是单纯的最新一条。注意一点:多级排序会把CASE表达式写得很长,建议先在SELECT里验证rn的分布情况,确认无误后再改成DELETE。4.3 删除前的备份和事务控制删生产数据前不备份,等于拿职业生涯开玩笑。我固定动作是两步:-- 第一步:完整备份要清理的表 SELECT * INTO 订单表_备份_20250115 FROM 订单表; -- 第二步:启动事务执行删除 BEGIN TRAN; WITH ranked AS (... ) DELETE FROM ranked WHERE rn 1; -- 检查影响行数 SELECT ROWCOUNT AS 删除行数; -- 确认无误后提交,有问题就回滚 -- ROLLBACK; COMMIT;SELECT INTO是最快的备份方式,直接把整个表复制成一张新表,不需要提前建表。等删除确认没问题后,备份表可以保留一段时间再删。事务的好处是防止手滑:万一删除后统计发现数据量不对,直接ROLLBACK回到删除前状态,不用去还原数据库。删除操作在SQL Server里默认隐式事务也很常见,但显式BEGIN TRAN更稳妥,尤其是批量删几十万行的时候。4.4 不直接删除,而是归档到非业务表有些公司审计要求严格,不允许物理删除数据。这种情况下我用OUTPUT子句把删除的记录同时写入归档表。SQL Server的DELETE ... OUTPUT可以捕获被影响的完整行:-- 需要先手动建好归档表,列结构与订单表一致 WITH ranked AS ( SELECT OrderID, OrderNo, Amount, CreatedAt, ROW_NUMBER() OVER(PARTITION BY OrderNo ORDER BY CreatedAt DESC) AS rn FROM 订单表 ) DELETE FROM ranked OUTPUT DELETED.* INTO 订单表_重复归档 WHERE rn 1;这条语句把删除和归档合成一步完成,归档表里保留着被删掉的每一条原始数据,方便日后追溯。归档表没有的话,可以提前用SELECT TOP 0 * INTO 订单表_重复归档 FROM 订单表 WHERE 1 0生成一个同结构的空表。像我处理过的一个零售客户,ERP本身没有保留重复数据的流转日志,归档操作帮他们后来追查上游接口bug省了大力气。4.5 清理后的三查:确认真的干净了删完之后别急着收工,我固定做三项核对:一是查重复是否清零:SELECT OrderNo FROM 订单表 GROUP BY OrderNo HAVING COUNT(*) 1,返回空才合格。二是查总量是否合理:删除行数 清理前重复条数 - 清理后重复键个数,如果对不上说明排序或分组逻辑有问题。三是抽查业务指标:比如订单总金额、总行数与备份表对比,确认没有误删非重复数据。这三项都过了,才算真正处理完一批重复数据。5. 大表与生产环境下排查重复的性能问题数据量小的表怎么折腾都行,一旦表里几百万、几千万行,重复统计和清理就没那么简单了。我接过好几个慢查询案例,原因基本集中在全表扫描和排序溢出上。5.1 为什么GROUP BY在大表上会慢GROUP BY需要把表按分组字段重新组织,SQL Server通常采用哈希聚合或流聚合,但都得先把相关列从磁盘读出来。如果没有任何索引,那就是名副其实的全表扫描。更隐蔽的问题是:当分组结果的中间集超过内存阈值,聚合会 spill 到 tempdb,性能会断崖式下跌。统计重复时最常见的慢SQL长这样:SELECT DeptID, COUNT(*) FROM 员工记录表 GROUP BY DeptID HAVING COUNT(*) 1;如果员工记录表有1亿行,这条SQL即使逻辑正确,执行时间也可能从秒级变成分钟级。5.2 用覆盖索引精准提速绝大多数重复统计都能靠一个设计得当的索引解决。思路是让索引覆盖GROUP BY和SELECT涉及的全部字段,避免回表。比如订单表经常按OrderNo统计重复,还经常取Amount、CreatedAt,那就建:CREATE NONCLUSTERED INDEX IX_订单表_OrderNo_Include ON 订单表(OrderNo) INCLUDE (OrderID, Amount, CreatedAt);创建这个索引以后,GROUP BY OrderNo配合COUNT(*)、SUM(Amount)时,SQL Server可以直接在索引上完成聚合,不需要回原表查数据,IO会明显减少。我曾在一个700万行的会员充值流水表上做类似优化,重复统计从8分钟降到15秒左右,效果立竿见影。注意,NCI索引的键列顺序尽量贴合WHERE和GROUP BY的谓词分布,包含列不要加太多,否则索引本身膨胀,写入性能反受影响。5.3 大表分批删除,避免锁竞争和日志暴涨几百万行重复数据一次性DELETE,轻则锁住整表,重则事务日志爆炸。SQL Server的DELETE TOP可以控制单批删除行数,配合WHILE循环,批处理稳妥得多:SET NOCOUNT ON; WHILE 1 1 BEGIN DELETE TOP (5000) o FROM 订单表 o INNER JOIN ( SELECT OrderID, ROW_NUMBER() OVER(PARTITION BY OrderNo ORDER BY CreatedAt DESC) AS rn FROM 订单表 WITH (NOLOCK) ) x ON o.OrderID x.OrderID WHERE x.rn 1; IF ROWCOUNT 0 BREAK; -- 批间停一下,给其他事务留窗口 WAITFOR DELAY 00:00:01; END注意几处细节:子查询里用ROW_NUMBER提前计算需要保留的优先级,DELETE TOP (5000)限制每批最多5000行,WAITFOR DELAY让每批之间停顿一秒,其他查询不至于长时间阻塞。如果表非常大,建议先把待删ID集合落地到临时表,再删除时临时表主键与目标表主键关联,可以显著减少子查询重复扫描的代价。5.4 生产环境操作的几个习惯操作生产环境,除了性能还要考虑安全:低峰期操作,并且提前沟通变更窗口。先用COUNT(*)评估重复规模,再推算批次数量,控制整体时间。批处理脚本里加TRY...CATCH,任何一批报错立即终止。事务日志提前留足空间,大批量删改前和DBA确认日志备份策略。有一次我在客户现场处理他们的短信发送日志表,大概3000万行,重复量有140万。一次性删除跑了一分多钟都没结束,还差点把日志目录撑爆。后来改成TOP 5000分批删除,每批间隔500毫秒,稳定执行了几分钟,期间业务方基本无感知。这个案例我记了很久:再简单的删除,在大数据量下也必须按批来。6. 完整实战:一份销售订单表的重复统计与汇总全流程前面讲了很多原则和方法,最后用一个我曾经处理过的实际场景把整个流程串起来。为了便于说明,表结构和数据我都做了脱敏和简化。6.1 需求背景和数据情况客户的数据表是销售订单明细表SalesOrder(OrderID,OrderNo,CustomerID,ProductID,Amount,Status,CreatedAt,Remark),总记录22294条,字段一共13个,需求方要求统计一个月内的重复记录并汇总成一个核对报表。一开始我怀疑只有几行重复,没想到一查吓了一跳。6.2 第一步:先摸清重复规模先做全量重复键统计,不要一上来就动手删。我写的排查SQL如下:SELECT OrderNo, COUNT(*) AS 总记录数, COUNT(DISTINCT CustomerID) AS 客户数, COUNT(DISTINCT ProductID) AS 产品数, COUNT(DISTINCT Amount) AS 金额种类, MIN(CreatedAt) AS 最早时间, MAX(CreatedAt) AS 最晚时间 FROM SalesOrder WHERE CreatedAt DATEADD(DAY, -30, GETDATE()) GROUP BY OrderNo HAVING COUNT(*) 1 ORDER BY COUNT(*) DESC;结果发现最高一个单号有4条记录,总共涉及432个重复单号、1023条重复行。重点是看到一部分单号金额种类 2,说明同单号的金额并不一致,这些必须单独交给业务核对,不能直接删除。纯重复的那些单号金额种类 1,可以继续往下走自动合并。6.3 第二步:生成重复汇总核对报表业务方要的不是一堆原始行,他们要看哪些单号重复了、重复了多少次、金额差异多大、ID是什么。我写了一条汇总SQL,把重复记录按单号合并成一行:SELECT OrderNo, COUNT(*) AS 重复条数, CONVERT(DECIMAL(10,2), MIN(Amount)) AS 最小金额, CONVERT(DECIMAL(10,2), MAX(Amount)) AS 最大金额, CONVERT(DECIMAL(10,2), MAX(Amount) - MIN(Amount)) AS 金额差异, STRING_AGG(CONVERT(VARCHAR(20), OrderID), ,) AS 明细ID, STRING_AGG(CONVERT(VARCHAR(23), CreatedAt, 120), ; ) AS 创建时间列表 FROM SalesOrder WHERE CreatedAt DATEADD(DAY, -30, GETDATE()) GROUP BY OrderNo HAVING COUNT(*) 1 ORDER BY 重复条数 DESC;金额差异这个字段很关键,值为0就是纯重复插入,直接清理;不为0就是同一单号金额不同,必须先去对账。这张报表导出后给业务方核对,对方一看就知道哪些要留、哪些可以清。汇总后还能进一步统计:重复单号占总单号的比例、重复行数占总行数的比例、重复金额虚增的总量,这些指标让管理层能直观看到重复数据的危害程度。6.4 第三步:清理重复,保留有效单据业务方核准后,清理规则定为:每个OrderNo保留CreatedAt最早、Amount非空的一条记录(因为最早那条是源头下单记录,后面的重复是导入脚本重试造成的)。执行前先备份,然后启动事务,用窗口函数定位待删行:SELECT * INTO SalesOrder_备份_20250115 FROM SalesOrder; BEGIN TRAN; WITH ranked AS ( SELECT OrderID, ROW_NUMBER() OVER( PARTITION BY OrderNo ORDER BY CASE WHEN Amount IS NOT NULL THEN 0 ELSE 1 END, CreatedAt ASC ) AS rn FROM SalesOrder ) DELETE FROM ranked WHERE rn 1; SELECT ROWCOUNT AS 删除行数; -- 核对无误后提交 COMMIT;执行完删除行数1023,和第一步统计的重复行数完全一致,说明分组逻辑没有偏差。随后做了三查:重复单号剩余为0,总行数等于22294减1023,订单金额总和与备份表核对后差额只来自被删的纯重复行。整个清理过程顺利结束,全程没有影响业务使用。6.5 这次的复盘经验回头看这个案例,处理顺序很重要:先摸清重复规模和结构,再汇总出核对报表让业务确认,最后才操作删除。很多人栽跟头是因为跳过了汇总核对这一步,直接写出DELETE一执行,结果误删了金额不同的有效单,事后只能靠备份恢复。数据量不大不代表问题不大,22294条记录里就有4.5%的重复率。重复数据一旦混入统计报表,对业务决策的误导是放大的。所以碰上这类需求,不要只想着删掉完事,把统计、汇总、核对、清理四步走完整,才是稳妥的做法。最后分享一个我个人的习惯:只要动重复数据,哪怕只是统计一下,也先把原始表备份好再操作。数据是公司的资产,多一步备份、多一句确认,后面就能少很多麻烦。