做SQL Server开发和维护这些年我处理过的“数据洁癖”需求里统计与汇总重复记录大概能排进前三。没做过的人以为就是一句COUNT(*)加GROUP BY再配HAVING真的上过生产环境的人都知道这活儿难点从来不在SQL语法而在你压根不知道数据是怎么变成这样、哪一列组合才算是“重复”、清完之后下游会不会崩。这篇文章我把几个高频场景完整拆一遍怎么界定重复、怎么写统计查询、怎么把重复明细抓出来汇总、怎么在千万级表上跑得动以及清理过程中那些坑——适合正在排查脏数据的开发、运维和数据分析同学参考。1. 什么样的重复才算重复动手前先定好规则1.1 重复数据到底是怎么混进来的先聊聊我见过的重复数据来源。SQL Server数据库里出现重复记录大概率不是心血来潮而是从上游就开始乱。最常见的是接口重发业务系统A调接口给BA那边超时了自动重试B收到请求后插入成功但响应丢失A再来一遍又插一条。这种问题一般靠幂等键解决但老系统打通时往往没人管。还有手动补录。运营后台表单没做提交状态控制鼠标多点两下事务跑了两次数据就重复了。再就是数据迁移从Excel导或从老库搬源数据里本身有重复迁移脚本又没有提前去重一次导入全进来了。最后是定时任务调度没做唯一性保护同一个批次跑了两次日志和结果表全重了。这些场景都有一个共同点只靠应用层保证没在数据库约束层兜底。所以我给团队讲的第一个原则是能加唯一索引的地方必须加。唯一索引是最后一道防线应用层的重试、并发、人为误操作再厉害也闯不进这条防线。1.2 一张老表的三种“重复”定义同样是“重复”业务上的含义差别很大。我一般会把重复分成三类完全重复整行所有列的值都一样部分重复关键列一样、其他列可能不同业务重复虽然没有完全一样的行但对业务来说同一客户同一时间下了一模一样的单就是重复。这个区分很重要。因为不同的“重复类型”统计SQL完全不同。完全重复用GROUP BY全列就能查部分重复则要先把业务键定出来。很多新人上来就对所有列GROUP BY结果查了半天一个重复都没有——因为时间戳精确到毫秒每一行都不可能完全一样。实际上真正该担心的是业务键重复比如同一个合同号出现两条同一个手机号注册两个账号这些在数据库里很可能并不是“全行相同”。所以写统计SQL之前一定先和业务对一遍这个表里什么情况下算一条合法记录哪几列组合起来应该唯一这个答案没有后面的SQL都是瞎写。1.3 统计重复前先问业务三个问题我在实际项目里会在动手前问三个问题基本每次都问出点东西来。第一个问题重复记录的保留策略是什么是保留最早一条、最晚一条还是留状态最新的一条最怕的是有人跟我说“随便留一条”真删完就出问题了——客户订单表里同单号两条记录一条是已取消、一条是已完成按ID最小保留可能就把有效那条删了。第二个问题这次统计范围是全表还是某个时间段线上业务表如果跑全表统计往往会把历史正确数据也卷进来造成极大的临时空间占用。第三个问题下游有没有依赖这张表的视图、存储过程和报表删数据之前最好先查一下系统视图里的依赖关系否则清完数据报表对不上锅还是得自己背。这三点都明确了再去写统计SQL基本不会返工。2. 统计重复记录的三条核心SQL可直接抄2.1 单字段重复GROUP BY HAVING COUNT 大于 1先写最简单的。假设员工表里email列应该唯一要找出重复的邮箱SQL是SELECT email, COUNT(*) AS cnt FROM dbo.employee GROUP BY email HAVING COUNT(*) 1 ORDER BY cnt DESC;这一句的要点有三处。GROUP BY把email相同的行揉成一组COUNT()数每组行数HAVING是过滤组相当于对分组结果的WHERE过滤。为什么不直接用WHERE COUNT() 1因为聚合函数不能出现在WHERE里执行顺序上WHERE在分组之前这时候每组还没产生。有人喜欢写COUNT(1)其实COUNT()和COUNT(1)在SQL Server里没有实质性能差异都可以。真正要注意的是COUNT(column)和COUNT()的区别COUNT(column)不统计NULL值如果email列允许NULL两条NULL的记录COUNT(email)会得出0而COUNT(*)是2。这时候重复记录就悄然漏掉了。2.2 多字段组合重复GROUP BY多列与汇总统计业务里更常见的是几个字段一起决定唯一性。比如订单表的customer_no和order_date同一客户同一天名下多条订单可能是合法的但如果同一个客户同一天同一商品出现两条就极其可疑。这时候GROUP BY两到三列SELECT customer_no, goods_no, order_date, COUNT(*) AS cnt FROM dbo.orders GROUP BY customer_no, goods_no, order_date HAVING COUNT(*) 1 ORDER BY cnt DESC, order_date DESC;GROUP BY多列的底层逻辑是把多个列拼成一个组合键每个唯一组合是一组。这里有个细节SQL Server的GROUP BY不支持使用SELECT列表里的别名比如GROUP BY customer_no这种没问题但如果SELECT里写了CASE表达式GROUP BY还得把整个表达式再写一遍不能直接写别名。统计出重复之后往往还要做一层汇总给领导看影响面。比如我常用的写法是外面套一层计算重复组的数量、受影响的行数和多出来的冗余行数SELECT COUNT(*) AS duplicate_group_count, SUM(cnt) AS affected_row_count, SUM(cnt - 1) AS extra_row_count FROM ( SELECT customer_no, goods_no, order_date, COUNT(*) AS cnt FROM dbo.orders GROUP BY customer_no, goods_no, order_date HAVING COUNT(*) 1 ) d;这样能快速知道有多少组重复、一共影响多少行、多出来的冗余行是几行写进修复报告里也直观。2.3 完全重复行检测GROUP BY 全列与 CHECKSUM 方案如果表里没有明确的主键而你怀疑存在整行完全相同的记录最直接的办法是GROUP BY所有列SELECT col1, col2, ..., colN, COUNT(*) AS cnt FROM dbo.log_table GROUP BY col1, col2, ..., colN HAVING COUNT(*) 1;列多的时候手都要写酸而且统计查询会变得很笨重。我把这种SQL封装成通用检测模板直接用CHECKSUM_BINARY所有列生成一个哈希值再按哈希分组SELECT CHECKSUM_BINARY(*) AS row_hash, COUNT(*) AS cnt FROM dbo.log_table GROUP BY CHECKSUM_BINARY(*) HAVING COUNT(*) 1;这里CHECKSUM_BINARY(*)会把整行的值压成一个整数性能上比GROUP BY几十个列好很多。但必须提醒一句CHECKSUM系列函数不是加密哈希是有碰撞可能的不同内容可能算出同一个值。所以它适合做初步筛选真要去重还得再用具体字段复核一遍。想要更稳可以用HASHBYTES结合XML拼接的写法开销更大但碰撞概率更低一般数据量够大且有争议的时候才用。2.4 把重复标记加进明细窗口函数 ROW_NUMBER统计只是第一步很多时候业务需要看到“每一行到底是不是重复、排第几”。窗口函数是这类问题目前最顺手的工具SELECT order_id, customer_no, goods_no, order_date, ROW_NUMBER() OVER (PARTITION BY customer_no, goods_no, order_date ORDER BY order_id) AS rn FROM dbo.orders;PARTITION BY把分组条件写在这里ORDER BY order_id表示组内按ID排序rn为1的就是该组保留行rn大于1的就是重复行。这个结果可以直接套一层WHERE rn 1把重复明细摘出来也可以在此基础上做删除。相比GROUP BY它的优势是能保留原表所有字段利于人工核查被统计的重复记录具体长什么样。3. 实战演练从查重复到清重复的完整流程3.1 造一个演示环境建表和插入重复数据为了演示完整流程我先建一张模拟订单表再故意插入几条重复数据。这段脚本在测试库可以原样跑。USE TestDB; GO CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) PRIMARY KEY, customer_no VARCHAR(20) NOT NULL, goods_no VARCHAR(20) NOT NULL, order_date DATE NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL ); GO INSERT INTO dbo.orders (customer_no, goods_no, order_date, amount, status) VALUES (C001, G001, 2024-01-05, 199.00, 已完成), (C001, G001, 2024-01-05, 199.00, 已完成), (C001, G002, 2024-01-06, 59.00, 已完成), (C002, G001, 2024-01-06, 199.00, 已完成), (C002, G001, 2024-01-06, 199.00, 已完成), (C002, G001, 2024-01-06, 199.00, 已完成), (C003, G003, 2024-01-07, 299.00, 已取消), (C004, G004, 2024-01-08, 99.00, 已完成);数据里故意放了三种情况C001有一组两条完全重复C002有一组三条完全重复C003虽然只有一条记录但状态是已取消后面可以用来演示不同业务规则的取舍。3.2 案例一监控日志表按小时汇总重复事件把场景换到运维侧。我们线上有张监控事件表存储各服务上报的告警。一次故障期间客户端重试上报了多条相同事件形成重复。需求按服务名和事件类型聚合统计每个重复组的发生次数和影响时间范围。SELECT service_name, event_type, event_time_hour, COUNT(*) AS repeated_times, MIN(event_time) AS first_time, MAX(event_time) AS last_time FROM dbo.monitor_event GROUP BY service_name, event_type, event_time_hour HAVING COUNT(*) 1;这里我把event_time截断到小时作为时间维度再对服务名和事件类型分组。统计出来的是每个“服务-类型-小时”桶里的重复条数配合MIN和MAX能看出重复事件横跨的时间范围。这个统计结果本身就是一张汇总报表可以直接导出给值班同学决定是否需要人工处理。这类监控表通常数据量大、保留期短统计时一定带上时间过滤条件比如event_time DATEADD(DAY, -7, GETDATE())否则扫描全表会让监控数据库的查询变慢甚至影响告警写入。3.3 案例二订单表重复记录排查与清理回到订单表我们要删除重复记录保留每个重复组里order_id最小的那一条。安全起见我会先跑一遍统计确认再写清理语句。第一步是统计确认SELECT customer_no, goods_no, order_date, COUNT(*) AS cnt FROM dbo.orders GROUP BY customer_no, goods_no, order_date HAVING COUNT(*) 1;第二步是清理。用CTE配合ROW_NUMBER生成组内序号删除序号大于1的行WITH cte AS ( SELECT order_id, ROW_NUMBER() OVER ( PARTITION BY customer_no, goods_no, order_date ORDER BY order_id ) AS rn FROM dbo.orders ) DELETE FROM cte WHERE rn 1;这段SQL的删除逻辑非常好理解CTE里的ROW_NUMBER把每个重复组内的行编号第一条保留后面都删。DELETE直接针对CTE操作SQL Server允许这样按窗口函数结果删数据实际执行的时候它会转化成对原表的删除操作。但我要强调一个极易翻车的点保留策略不是我拍的而是业务定的。如果业务要求保留状态为“已完成”的那条那ORDER BY就得改成CASE WHEN status 已完成 THEN 0 ELSE 1 END把优先级高的排到前头。更稳妥的做法是清理前先把不需要的行单独SELECT出来人工看一眼确认无误再删。3.4 案例三时间序列里相邻重复记录的检测还有一种重复不是“多条相同分组”而是时间序列里的连续重复。比如设备每隔5分钟上报一次电流值网络抖动时同一数值连续上报了好多次我们需要找出连续重复的时间段。检测连续重复窗口函数LAG比GROUP BY好用。LAG可以取到当前行之前的某一行的值和当前行比一下就知道是不是和前面重复WITH t AS ( SELECT device_id, record_time, current_value, LAG(current_value) OVER (PARTITION BY device_id ORDER BY record_time) AS prev_value, LAG(record_time) OVER (PARTITION BY device_id ORDER BY record_time) AS prev_time FROM dbo.device_readings ) SELECT device_id, prev_time AS repeat_start, record_time AS repeat_end, current_value FROM t WHERE current_value prev_value;这个写法把“和上一条记录值相同”的行筛出来。如果连续重复三段会查出两条相邻重复记录再做一次连续区间标记就能汇总出完整连续区间。这个场景在设备数据、行情数据和监控采样里很常见GROUP BY那一套按固定键分组的方法在这里完全失效因为它不关心时间相邻性。4. 数据量大时的性能优化与索引设计4.1 为什么几千行很快、几百万行就卡死同样的GROUP BYHAVING在几千行的表上秒出放到几百万行的表上可能跑几分钟。原因是统计重复需要对全表数据做分组聚合。SQL Server的优化器会基于成本选择聚合算法有合适的索引可以用Stream Aggregate没有就用Hash Aggregate。Hash Aggregate需要把数据按哈希值分发到多个内存桶里处理内存不够时会溢出到tempdb于是查询变慢还可能拖慢同实例上其他任务。另外COUNT(*)这种聚合不会用到索引统计里的密度信息查询计划经常是全表扫描几百万行数据等于把每一行读一遍再算一遍。如果你还SELECT了很多业务字段扫出来就是几十GB的IO慢得理所当然。所以面对大数据量第一反应不是改SQL而是看是不是该加索引是不是能缩小范围。4.2 为重复统计场景设计索引的正确姿势给重复统计配置索引核心原则是分组列优先。比如经常按customer_no、goods_no、order_date分组统计就建这样的索引CREATE NONCLUSTERED INDEX IX_orders_dup ON dbo.orders (customer_no, goods_no, order_date) INCLUDE (order_id, amount, status);这里的逻辑是索引的键列顺序和GROUP BY里列的顺序一致让数据在索引里已经排好序INCLUDE再加几个查询需要展示的列让索引覆盖查询避免每行都回表取数据。如果统计语句只需要customer_no、goods_no、order_date和COUNT(*)那连INCLUDE都不需要索引键本身够用了。但要注意唯一索引才是釜底抽薪。如果表中已经能确定重复组直接建唯一索引CREATE UNIQUE NONCLUSTERED INDEX ... ON dbo.orders (customer_no, goods_no, order_date); 它会立刻拒绝后续的重复写入。不过做这件事的前提是当前表已经没有重复否则索引创建会失败。所以流程一般是先统计出重复、清理掉、再建唯一索引兜底。4.3 分批处理和临时表别让一次查询拖垮生产生产环境几千万行的订单表就算有索引统计全表依然煎熬。我的做法是分批次处理。按order_id的区间切段比如每次处理100万行用循环来处理DECLARE batch_size INT 1000000; DECLARE min_id INT, max_id INT; SELECT min_id MIN(order_id), max_id MAX(order_id) FROM dbo.orders; WHILE min_id max_id BEGIN -- 统计这一批的重复结果 INSERT INTO #dup_result (...) SELECT ... FROM dbo.orders WHERE order_id min_id AND order_id min_id batch_size GROUP BY ...; SET min_id min_id batch_size; END分批的好处是每次锁定的行数少不长时间占用资源对同实例上的在线业务冲击小。坏处是写起来啰嗦而且要小心批次边界同一个重复组可能跨两个批次因此分组键不能只在本批内统计。稳妥的姿势是先把可疑分组键抽到临时表再回到原表做明细匹配。比如第一轮先用较轻量的查询把重复组键摘出来第二轮再按这些键回原表取明细这样避免一次全表GROUP BY。5. 常见问题与排查避坑实录5.1 分组列里的NULL让结果悄悄少了最典型的一个坑。假设客户表mobile字段允许NULL你要统计有没有重复手机号。如果GROUP BY mobile所有NULL值是会被分到同一组里的COUNT(*)会显示有多少条NULL记录。但如果你写COUNT(mobile)那一组会显示0——因为COUNT(列)不算NULL。更隐蔽的情况是你统计时对字符串列用了RTRIMRTRIM(NULL)还是NULL分组计算本身不会报错但结果就会和预期不一致。实际排查时我习惯把NULL单独拎出来看一遍SELECT * FROM dbo.customer WHERE mobile IS NULL; 确认这些记录到底算不算重复。很多业务系统里NULL和不存在的值在导出后都变成空字符串但库里两者是分开的统计前必须统一COALESCE否则你会把NULL和空字符串重复漏掉或者反过来误判成重复。5.2 排序规则害的大小写导致的重复误判SQL Server的字符串比较行为由排序规则Collation决定。默认中文环境常用Chinese_PRC_CI_ASCI表示大小写不敏感AS表示重音敏感。在这种规则下Abc和abc会被判断为相同如果你不去重两条记录就一起被COUNT成2。反过来如果有些表用了大小写敏感的排序规则Abc和abc就是不同的你的统计里它们各归一组看不出重复。做统计前先确认排序规则SELECT name, collation_name FROM sys.databases; 如果业务上明确要求区分大小写而表的排序规则不区分你可以在查询时强制转换排序规则GROUP BY col COLLATE Latin1_General_CS_AS。这个技巧平时用不到但一旦遇到客户说“我们有两个账号就差大小写”你就知道它值多少钱了。5.3 统计对不上账浮点、时区和四舍五入还有一类“统计和业务对不上”的重复问题纯粹是数据类型惹的祸。金额用DECIMAL(18,2)没问题怕的是有人用FLOAT存金额。FLOAT是二进制近似存储0.1和0.10在FLOAT里可能不是同一个数分组统计时原本该归为一组的金额被拆成了好几组重复查不出来。时间字段的坑是时区。同一笔订单业务库存的是北京时间报表库转成UTC后多出来几行相同键的记录一统计就“重复”了。这种情况不怪SQL怪数据源头不统一。我的处理方式是先做一次数据质量核查脚本把重复统计结果的每条记录打出来看时间偏移量是否固定如果是固定8小时基本能断定是时区转换问题。5.4 误删重复数据之后的补救流程清理重复数据最怕的是手滑删多了。我见过有人DELETE语句漏写了WHERE条件整个表数据全没了。遇到这种事故第一件事是停止生产写入不要让新事务覆盖日志尤其别急着收缩数据库文件。如果数据库是完整恢复模式第一时间考虑从备份做还原或者做日志尾部备份后点时间点恢复。如果没有备份但事务日志还在可以尝试专业的数据恢复工具——市面上有不少专门针对MS SQL Server做页级解析的恢复产品对于有事务日志但主库备份缺失的场景工具通常能解析日志页内容找回已经删除的数据。这类工具属于兜底方案能不能恢复取决于日志是否被截断、物理文件是否完好。比所有恢复方案都有效的只有一条动手清理之前先把受影响的数据备份一份。哪怕是执行DELETE前一条简单的SELECT INTO备份SELECT * INTO dbo.orders_backup_20240115 FROM dbo.orders;这一句花不了几十秒但能让所有后续操作都有后悔药。我处理过的严重事故里凡是备份了再动的最后都毫发无损图省事直接删的没几个结局好看。写了这么多最后说点个人体会。统计与汇总重复记录这件事SQL只是手段真正难的是对业务数据的理解。我每次都是先统计、再确认业务规则、最后才动清理顺序反了就得返工甚至收不了场。如果你也在排查一张满是怪数据的表建议先把文里这几条SQL跑一遍重点是那几个NULL、Collation和时间类型的坑。稳扎稳打比什么骚操作都管用。