Oracle 分组取最新记录去除重复数据的 4 种方案当表中某列存在重复值需要按时间字段获取每组中最新的那一条数据。本文对比 ROW_NUMBER()、KEEP DENSE_RANK、子查询、GROUP BY JOIN 四种方案覆盖完整实战场景。一、问题描述1.1 典型场景-- 设备检测信息表 DEVICE_INFO_TBL-- 每个设备每天产生一条检测记录-- 现在需要检索每个设备的最新检测信息device_idtest_resultupdate_dtmDEV001正常2024-02-20 10:00:00DEV001告警2024-02-21 14:30:00DEV002正常2024-02-19 09:00:00DEV002故障2024-02-21 08:00:00DEV003正常2024-02-20 16:00:00目标device_id去重取每个设备update_dtm最新的那条记录。二、方案一ROW_NUMBER()推荐2.1 核心 SQLSELECTT.*FROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBY需要分组的字段ORDERBY时间字段DESC)ASRNFROM表 AWHERE条件)TWHERET.RN1;2.2 完整示例SELECTT.*FROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBYA.device_idORDERBYA.update_dtmDESC)ASRNFROMdevice_info_tbl AWHEREA.test_resultISNOTNULL)TWHERET.RN1;2.3 ROW_NUMBER() 执行流程原始数据按 device_id 分组按 update_dtm 排序 ┌───────────┬──────────────┬─────────────────────┬────┐ │ device_id │ test_result │ update_dtm │ RN │ ├───────────┼──────────────┼─────────────────────┼────┤ │ DEV001 │ 告警 │ 2024-02-21 14:30:00 │ 1 │ ← 选中 │ DEV001 │ 正常 │ 2024-02-20 10:00:00 │ 2 │ ├───────────┼──────────────┼─────────────────────┼────┤ │ DEV002 │ 故障 │ 2024-02-21 08:00:00 │ 1 │ ← 选中 │ DEV002 │ 正常 │ 2024-02-19 09:00:00 │ 2 │ ├───────────┼──────────────┼─────────────────────┼────┤ │ DEV003 │ 正常 │ 2024-02-20 16:00:00 │ 1 │ ← 选中 └───────────┴──────────────┴─────────────────────┴────┘2.4 优缺点维度说明优点语法直观支持多字段分组可扩展为取 Top N缺点需要子查询Oracle 12c 支持QUALIFY简化性能分组字段有索引时表现良好适用通用场景推荐首选三、方案二KEEP DENSE_RANKOracle 专有3.1 核心 SQLSELECTdevice_id,MAX(test_result)KEEP(DENSE_RANKFIRSTORDERBYupdate_dtmDESC)AStest_result,MAX(update_dtm)ASupdate_dtmFROMdevice_info_tblGROUPBYdevice_id;3.2 语法说明AGGREGATE_FUNC(column)KEEP(DENSE_RANKFIRST/LASTORDERBYsort_column[ASC|DESC])元素含义AGGREGATE_FUNC聚合函数MAX、MIN、SUM、COUNT、AVG等KEEP关键字表示只对满足排序条件的行进行聚合DENSE_RANK FIRST取排名第一的行DENSE_RANK LAST取排名最后的行ORDER BY定义排名的排序规则3.3 完整示例-- 取每个设备的最新检测结果SELECTdevice_id,MAX(test_result)KEEP(DENSE_RANKFIRSTORDERBYupdate_dtmDESC)ASlatest_result,MAX(update_dtm)ASlatest_timeFROMdevice_info_tblGROUPBYdevice_id;-- 取每个设备最早最旧的检测结果SELECTdevice_id,MAX(test_result)KEEP(DENSE_RANKFIRSTORDERBYupdate_dtmASC)ASearliest_result,MIN(update_dtm)ASearliest_timeFROMdevice_info_tblGROUPBYdevice_id;-- 示例同时取最早和最新SELECTdevice_id,MAX(test_result)KEEP(DENSE_RANKFIRSTORDERBYupdate_dtmDESC)ASlatest_result,MAX(update_dtm)ASlatest_time,MAX(test_result)KEEP(DENSE_RANKFIRSTORDERBYupdate_dtmASC)ASearliest_result,MIN(update_dtm)ASearliest_timeFROMdevice_info_tblGROUPBYdevice_id;3.4 优缺点维度说明优点单层查询无需子查询性能优秀缺点Oracle 专有语法不可移植只能取一个字段的最值适用Oracle 环境且只需要取少数几个字段四、方案三子查询 MAX(time)4.1 核心 SQLSELECTA.*FROM表 AJOIN(SELECT分组字段,MAX(时间字段)ASmax_timeFROM表WHERE条件GROUPBY分组字段)BONA.分组字段B.分组字段ANDA.时间字段B.max_time;4.2 完整示例SELECTA.*FROMdevice_info_tbl AJOIN(SELECTdevice_id,MAX(update_dtm)ASmax_timeFROMdevice_info_tblWHEREtest_resultISNOTNULLGROUPBYdevice_id)BONA.device_idB.device_idANDA.update_dtmB.max_time;4.3 注意事项-- ⚠️ 如果同一时间戳有多条数据会返回多条-- 解决方案加上 ROW_NUMBER() 去重回到方案一SELECTT.*FROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBYA.device_id,A.update_dtmORDERBYA.rowid)ASRNFROMdevice_info_tbl AJOIN(SELECTdevice_id,MAX(update_dtm)ASmax_timeFROMdevice_info_tblGROUPBYdevice_id)BONA.device_idB.device_idANDA.update_dtmB.max_time)TWHERET.RN1;4.4 优缺点维度说明优点标准 SQL可移植性好MySQL、PostgreSQL 等通用缺点需要两次扫描表时间戳不唯一时可能多返数据适用非 Oracle 数据库或需要跨数据库兼容的 SQL五、方案四GROUP BY 列关联5.1 核心 SQLSELECTA.*FROM表 AWHEREA.时间字段(SELECTMAX(B.时间字段)FROM表 BWHEREB.分组字段A.分组字段);5.2 完整示例SELECT*FROMdevice_info_tbl AWHEREA.update_dtm(SELECTMAX(B.update_dtm)FROMdevice_info_tbl BWHEREB.device_idA.device_id);5.3 优缺点维度说明优点逻辑简单适合快速查询缺点大数据量性能极差关联子查询逐行执行适用小表千级以下不推荐生产环境六、四种方案对比6.1 综合对比方案可移植性性能复杂度表扫描次数支持 Top N① ROW_NUMBER()★★★★☆★★★★★★★☆☆☆1 次✅② KEEP DENSE_RANK★☆☆☆☆★★★★★★★★☆☆1 次❌③ 子查询MAX★★★★★★★★★☆★★★☆☆2 次❌④ GROUP BY关联★★★★☆★★☆☆☆★★☆☆☆N 次❌6.2 推荐选择┌──────────────┐ │ 需要去重取最新 │ └──────┬───────┘ ▼ ┌───────────────────────┐ │ 是否仅限 Oracle 数据库 │ └──────┬──────────┬─────┘ │ │ 是 否 │ │ ▼ ▼ ┌──────────┐ ┌──────────────┐ │ 只取1-2个 │ │ ROW_NUMBER() │ │ 字段 → │ │ OVER │ │ KEEP │ │ PARTITION BY │ │ DENSE_RANK│ └──────────────┘ ├──────────┤ │ 取全部 │ │ 字段 → │ │ ROW_NUM │ │ BER() │ └──────────┘七、进阶场景7.1 Top N每组取前 N 条-- 取每个设备最新的 3 条检测记录SELECT*FROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBYA.device_idORDERBYA.update_dtmDESC)ASRNFROMdevice_info_tbl A)TWHERET.RN3;7.2 多字段分组-- 按设备 类型分组取每组最新SELECT*FROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBYA.device_id,A.device_typeORDERBYA.update_dtmDESC)ASRNFROMdevice_info_tbl A)TWHERET.RN1;7.3 多字段排序去重-- 按时间降序时间相同时按 ID 降序SELECT*FROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBYA.device_idORDERBYA.update_dtmDESC,A.idDESC)ASRNFROMdevice_info_tbl A)TWHERET.RN1;7.4 排除最新记录取倒数第二-- 取每个设备的倒数第二条记录SELECT*FROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBYA.device_idORDERBYA.update_dtmDESC)ASRN,COUNT(*)OVER(PARTITIONBYA.device_id)ASCNTFROMdevice_info_tbl A)TWHERET.RN2;7.5 Oracle 12c 的 QUALIFY 语法-- Oracle 12c 支持 QUALIFY 简化 ROW_NUMBER()SELECTA.*FROMdevice_info_tbl A QUALIFY ROW_NUMBER()OVER(PARTITIONBYA.device_idORDERBYA.update_dtmDESC)1;八、性能优化建议8.1 索引策略-- 对分组字段 排序字段创建复合索引CREATEINDEXidx_device_timeONdevice_info_tbl(device_id,update_dtmDESC);-- 带条件的查询考虑条件字段放前面CREATEINDEXidx_status_device_timeONdevice_info_tbl(test_result,device_id,update_dtmDESC);8.2 执行计划关注点-- 查看执行计划EXPLAINPLANFORSELECTT.*FROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBYA.device_idORDERBYA.update_dtmDESC)ASRNFROMdevice_info_tbl A)TWHERET.RN1;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);执行计划指标好差访问方式INDEX RANGE SCANFULL TABLE SCAN排序方式INDEX SORTWINDOW SORT分区裁剪PARTITION RANGE全分区扫描8.3 数据量级建议数据量推荐方案预期耗时万级ROW_NUMBER() 索引 100ms十万级ROW_NUMBER() 索引 分页 500ms百万级分区表 ROW_NUMBER() 2s千万级物化视图/结果表预计算实时九、常见问题 FAQQROW_NUMBER() 和 RANK() 有什么区别AROW_NUMBER() 始终返回连续的唯一值1,2,3,4RANK() 在排序值相同时返回并列值1,1,3,4。RN ROW_NUMBER() → 1, 2, 3, 4 唯一连续 RK RANK() → 1, 1, 3, 4 可并列跳过后续位次 DR DENSE_RANK()→ 1, 1, 2, 3 可并列不跳过后续位次Q时间字段重复怎么办A在 ORDER BY 中追加唯一字段如 ID DESC作为二级排序条件。QKEEP DENSE_RANK 和 ROW_NUMBER 哪个快A通常 KEEP DENSE_RANK 略快单层查询但差异很小。推荐优先 ROW_NUMBER()。QMySQL 可以用 ROW_NUMBER() 吗AMySQL 8.0 支持 ROW_NUMBER()。MySQL 5.x 只能用方案三子查询MAX。Q如果只取最新的一条记录不分组A直接用ORDER BY time DESC FETCH FIRST 1 ROW ONLYOracle 12c。十、总结方案一句话总结ROW_NUMBER() 子查询通用方案支持 Top N推荐首选KEEP DENSE_RANKOracle 专有单层查询取单字段最值子查询 MAX标准 SQL跨数据库兼容GROUP BY 关联子查询简单小表可用大数据量不推荐一句话总结去重取最新用ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 时间字段 DESC)性能好、逻辑清晰、支持 Top N是最通用的最佳方案。