SQL查询:如何输出重复记录对应的所有原表行(含reference字段)
重复记录全量明细查询实现方案
兼容所有SQL版本的通用实现
这是不需要依赖特殊语法、在任何支持GROUP BY的数据库(包括你当前用的SQL Server)上都能直接运行的写法,核心逻辑是先筛出所有重复出现的name集合,再关联原表取出这些name对应的所有明细:
SELECT u.[name] AS Name, u.[reference] AS Reference FROM [user] u INNER JOIN ( -- 你原本写的重复值统计逻辑,仅作为过滤用的重复name集合 SELECT [name] FROM [user] GROUP BY [name] HAVING COUNT([name]) > 1 ) dup_names ON u.[name] = dup_names.[name] -- 按name、reference排序后,输出顺序和你期望的格式完全匹配 ORDER BY u.[name], u.[reference]
这种写法的优势:
- 完全复用你已经写好的聚合逻辑,不需要额外调整代码结构
- 数据库优化器可自动复用聚合阶段的表扫描结果,不会产生多余的全表读取开销
大表场景下的最优实现(支持窗口函数的数据库适用)
如果你的数据库版本支持窗口函数(SQL Server 2008及以上均可),这是性能最好的实现方式,整个查询只需要一次全表扫描,比上面的关联写法减少一半的IO开销:
SELECT [name] AS Name, [reference] AS Reference FROM ( SELECT [name], [reference], -- 按name分组计算每个name的总出现次数 COUNT(*) OVER (PARTITION BY [name]) AS occur_cnt FROM [user] ) t -- 直接过滤出出现次数大于1的行 WHERE occur_cnt > 1 ORDER BY [name], [reference]
这种写法的优势:
- 代码结构更简洁,没有多层关联的嵌套逻辑
- 千万级以上大表查询时,性能提升明显
避坑提醒
不要采用自连接、多层NOT IN嵌套的方式实现这个需求,这类写法在数据量稍大时会出现严重的性能问题,也更难排查错误。
内容的提问来源于stack exchange,提问作者Ryan Jackson
相关产品推荐
相关产品推荐

