Oracle 分组取最新记录：去除重复数据的 4 种方案

当表中某列存在重复值，需要按时间字段获取每组中最新的那一条数据。本文对比 ROW_NUMBER()、KEEP DENSE_RANK、子查询、GROUP BY + JOIN 四种方案，覆盖完整实战场景。

---

一、问题描述

1.1 典型场景

-- 设备检测信息表 DEVICE_INFO_TBL
-- 每个设备每天产生一条检测记录
-- 现在需要检索每个设备的最新检测信息

device_id	test_result	update_dtm
DEV001	正常	2024-02-20 10:00:00
DEV001	告警	2024-02-21 14:30:00
DEV002	正常	2024-02-19 09:00:00
DEV002	故障	2024-02-21 08:00:00
DEV003	正常	2024-02-20 16:00:00

目标： device_id 去重，取每个设备 update_dtm 最新的那条记录。

---

二、方案一：ROW_NUMBER()（推荐）

2.1 核心 SQL

SELECT T.*
FROM (
    SELECT A.*,
           ROW_NUMBER() OVER(
               PARTITION BY 需要分组的字段
               ORDER BY 时间字段 DESC
           ) AS RN
    FROM 表 A
    WHERE 条件
) T
WHERE T.RN = 1;

2.2 完整示例

SELECT T.*
FROM (
    SELECT A.*,
           ROW_NUMBER() OVER(
               PARTITION BY A.device_id
               ORDER BY A.update_dtm DESC
           ) AS RN
    FROM device_info_tbl A
    WHERE A.test_result IS NOT NULL
) T
WHERE T.RN = 1;

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_RANK（Oracle 专有）

3.1 核心 SQL

SELECT
    device_id,
    MAX(test_result) KEEP(DENSE_RANK FIRST ORDER BY update_dtm DESC) AS test_result,
    MAX(update_dtm) AS update_dtm
FROM device_info_tbl
GROUP BY device_id;

3.2 语法说明

AGGREGATE_FUNC(column)
    KEEP(DENSE_RANK FIRST/LAST ORDER BY sort_column [ASC|DESC])

元素	含义
AGGREGATE_FUNC	聚合函数：MAX、MIN、SUM、COUNT、AVG 等
KEEP	关键字，表示只对满足排序条件的行进行聚合
DENSE_RANK FIRST	取排名第一的行
DENSE_RANK LAST	取排名最后的行
ORDER BY	定义排名的排序规则

3.3 完整示例

-- 取每个设备的最新检测结果
SELECT
    device_id,
    MAX(test_result) KEEP(DENSE_RANK FIRST ORDER BY update_dtm DESC) AS latest_result,
    MAX(update_dtm) AS latest_time
FROM device_info_tbl
GROUP BY device_id;

-- 取每个设备最早（最旧）的检测结果
SELECT
    device_id,
    MAX(test_result) KEEP(DENSE_RANK FIRST ORDER BY update_dtm ASC) AS earliest_result,
    MIN(update_dtm) AS earliest_time
FROM device_info_tbl
GROUP BY device_id;

-- 示例：同时取最早和最新
SELECT
    device_id,
    MAX(test_result) KEEP(DENSE_RANK FIRST ORDER BY update_dtm DESC) AS latest_result,
    MAX(update_dtm) AS latest_time,
    MAX(test_result) KEEP(DENSE_RANK FIRST ORDER BY update_dtm ASC) AS earliest_result,
    MIN(update_dtm) AS earliest_time
FROM device_info_tbl
GROUP BY device_id;

3.4 优缺点

维度	说明
优点	单层查询，无需子查询，性能优秀
缺点	Oracle 专有语法，不可移植；只能取一个字段的最值
适用	Oracle 环境，且只需要取少数几个字段

---

四、方案三：子查询 + MAX(time)

4.1 核心 SQL

SELECT A.*
FROM 表 A
JOIN (
    SELECT 分组字段, MAX(时间字段) AS max_time
    FROM 表
    WHERE 条件
    GROUP BY 分组字段
) B ON A.分组字段 = B.分组字段
   AND A.时间字段 = B.max_time;

4.2 完整示例

SELECT A.*
FROM device_info_tbl A
JOIN (
    SELECT device_id, MAX(update_dtm) AS max_time
    FROM device_info_tbl
    WHERE test_result IS NOT NULL
    GROUP BY device_id
) B ON A.device_id = B.device_id
   AND A.update_dtm = B.max_time;

4.3 注意事项

-- ⚠️ 如果同一时间戳有多条数据，会返回多条
-- 解决方案：加上 ROW_NUMBER() 去重（回到方案一）
SELECT T.*
FROM (
    SELECT A.*,
           ROW_NUMBER() OVER(
               PARTITION BY A.device_id, A.update_dtm
               ORDER BY A.rowid
           ) AS RN
    FROM device_info_tbl A
    JOIN (
        SELECT device_id, MAX(update_dtm) AS max_time
        FROM device_info_tbl
        GROUP BY device_id
    ) B ON A.device_id = B.device_id AND A.update_dtm = B.max_time
) T
WHERE T.RN = 1;

4.4 优缺点

维度	说明
优点	标准 SQL，可移植性好（MySQL、PostgreSQL 等通用）
缺点	需要两次扫描表；时间戳不唯一时可能多返数据
适用	非 Oracle 数据库，或需要跨数据库兼容的 SQL

---

五、方案四：GROUP BY + 列关联

5.1 核心 SQL

SELECT A.*
FROM 表 A
WHERE A.时间字段 = (
    SELECT MAX(B.时间字段)
    FROM 表 B
    WHERE B.分组字段 = A.分组字段
);

5.2 完整示例

SELECT *
FROM device_info_tbl A
WHERE A.update_dtm = (
    SELECT MAX(B.update_dtm)
    FROM device_info_tbl B
    WHERE B.device_id = A.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 (
    SELECT A.*,
           ROW_NUMBER() OVER(
               PARTITION BY A.device_id
               ORDER BY A.update_dtm DESC
           ) AS RN
    FROM device_info_tbl A
) T
WHERE T.RN <= 3;

7.2 多字段分组

-- 按设备 + 类型分组，取每组最新
SELECT *
FROM (
    SELECT A.*,
           ROW_NUMBER() OVER(
               PARTITION BY A.device_id, A.device_type
               ORDER BY A.update_dtm DESC
           ) AS RN
    FROM device_info_tbl A
) T
WHERE T.RN = 1;

7.3 多字段排序去重

-- 按时间降序，时间相同时按 ID 降序
SELECT *
FROM (
    SELECT A.*,
           ROW_NUMBER() OVER(
               PARTITION BY A.device_id
               ORDER BY A.update_dtm DESC, A.id DESC
           ) AS RN
    FROM device_info_tbl A
) T
WHERE T.RN = 1;

7.4 排除最新记录（取倒数第二）

-- 取每个设备的倒数第二条记录
SELECT *
FROM (
    SELECT A.*,
           ROW_NUMBER() OVER(
               PARTITION BY A.device_id
               ORDER BY A.update_dtm DESC
           ) AS RN,
           COUNT(*) OVER(PARTITION BY A.device_id) AS CNT
    FROM device_info_tbl A
) T
WHERE T.RN = 2;

7.5 Oracle 12c+ 的 QUALIFY 语法

-- Oracle 12c+ 支持 QUALIFY 简化 ROW_NUMBER()
SELECT A.*
FROM device_info_tbl A
QUALIFY ROW_NUMBER() OVER(
    PARTITION BY A.device_id
    ORDER BY A.update_dtm DESC
) = 1;

---

八、性能优化建议

8.1 索引策略

-- 对分组字段 + 排序字段创建复合索引
CREATE INDEX idx_device_time ON device_info_tbl(device_id, update_dtm DESC);

-- 带条件的查询，考虑条件字段放前面
CREATE INDEX idx_status_device_time ON device_info_tbl(test_result, device_id, update_dtm DESC);

8.2 执行计划关注点

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT T.*
FROM (
    SELECT A.*,
           ROW_NUMBER() OVER(PARTITION BY A.device_id ORDER BY A.update_dtm DESC) AS RN
    FROM device_info_tbl A
) T
WHERE T.RN = 1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

执行计划指标	好	差
访问方式	INDEX RANGE SCAN	FULL TABLE SCAN
排序方式	INDEX SORT	WINDOW SORT
分区裁剪	PARTITION RANGE	全分区扫描

8.3 数据量级建议

数据量	推荐方案	预期耗时
万级	ROW_NUMBER() + 索引	< 100ms
十万级	ROW_NUMBER() + 索引 + 分页	< 500ms
百万级	分区表 + ROW_NUMBER()	< 2s
千万级	物化视图/结果表预计算	实时

---

九、常见问题 FAQ

Q：ROW_NUMBER() 和 RANK() 有什么区别？
A：ROW_NUMBER() 始终返回连续的唯一值（1,2,3,4）；RANK() 在排序值相同时返回并列值（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）作为二级排序条件。

Q：KEEP DENSE_RANK 和 ROW_NUMBER 哪个快？
A：通常 KEEP DENSE_RANK 略快（单层查询），但差异很小。推荐优先 ROW_NUMBER()。

Q：MySQL 可以用 ROW_NUMBER() 吗？
A：MySQL 8.0+ 支持 ROW_NUMBER()。MySQL 5.x 只能用方案三（子查询+MAX）。

Q：如果只取最新的一条记录（不分组）？
A：直接用 ORDER BY time DESC FETCH FIRST 1 ROW ONLY（Oracle 12c+）。

---

十、总结

方案	一句话总结
ROW_NUMBER() + 子查询	通用方案，支持 Top N，推荐首选
KEEP DENSE_RANK	Oracle 专有，单层查询，取单字段最值
子查询 + MAX	标准 SQL，跨数据库兼容
GROUP BY + 关联子查询	简单小表可用，大数据量不推荐

一句话总结： 去重取最新用 ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 时间字段 DESC)，性能好、逻辑清晰、支持 Top N，是最通用的最佳方案。