介绍一下 Oracle中的 ROWNUM 和 ROW_NUMBER OVER

目录

前言

一、ROWNUM：结果集的行号伪列

1. 基本特性

2. 常用场景：限制返回的行数

3. 分页的正确写法（结合 ROWNUM）

4. ROWNUM 的局限

二、OVER 与 ROW_NUMBER()：窗口函数中的行号

1. 基本语法

2. 典型例子：各部门按工资排序，从高到低编号

3. 与 ROWNUM 的核心区别

三、其它

OVER 英文 在这里如何解析

PARTITION BY 对应的英文是什么

什么叫 分析函数，为什又叫窗口函数

为什么 ，WHERE ROWNUM > 1 永远不会返回任何行

Oracle 中 PARTITION BY 的作用是什么

四、使用

删除要求

删除效果举例

================

前言

在 Oracle 数据库中，ROWNUM 和 OVER 是用于不同目的但经常被混淆的两个概念。简单来说：

• ROWNUM 是一个伪列，它在查询结果集返回时，为每一行分配一个唯一的、递增的行号。

• OVER 是分析函数（窗口函数）的一部分，它与 ROW_NUMBER()、RANK() 等函数配合，在保留所有原始行的基础上，进行分组、排序和编号。

下面详细拆解这两个概念。

================

一、ROWNUM：结果集的行号伪列

1. 基本特性

• ROWNUM 是 Oracle 特有的，在查询返回行的过程中动态生成。

• 第一行 ROWNUM = 1，第二行 ROWNUM = 2，依次类推。

• 关键点：ROWNUM 是在 WHERE 条件过滤之后、ORDER BY 排序之前分配的。也就是说，如果用了 ORDER BY，排序会改变行的物理顺序，但 ROWNUM 仍然是按照原始获取顺序分配的（除非使用子查询）。

2. 常用场景：限制返回的行数

-- 返回前 5 行（不等同于排序后的前 5）
SELECT * FROM employees WHERE ROWNUM <= 5;

注意：WHERE ROWNUM > 1 永远不会返回任何行，因为第一行被过滤后，第二行不会变成第一行（ROWNUM 始终从 1 开始）。所以要实现真正的分页，需要嵌套子查询。

3. 分页的正确写法（结合 ROWNUM）

SELECT * FROM (
    SELECT e.*, ROWNUM rn FROM (
        SELECT * FROM employees ORDER BY salary DESC
    ) e WHERE ROWNUM <= 20
) WHERE rn > 10;

4. ROWNUM 的局限

• 无法直接实现“每组内排名”。

• 与 ORDER BY 同时使用时容易出错（因为先分配 ROWNUM 再排序）。

• 属于 Oracle 特有，不兼容其他数据库（如 PostgreSQL、MySQL 用 LIMIT）。

二、OVER 与 ROW_NUMBER()：窗口函数中的行号

OVER 是分析函数的子句，用于定义窗口（即数据分组和排序的范围）。最常用的与行号相关的函数是 ROW_NUMBER()。

1. 基本语法

ROW_NUMBER() OVER ( [PARTITION BY 列1, 列2...] ORDER BY 列3 [ASC|DESC] )

• PARTITION BY：可选，将数据分成独立的分组（如部门、类别）。

• ORDER BY：在每个组内指定排序规则。

• ROW_NUMBER()：为每个组内的行分配一个唯一的、从1开始的连续编号。

=====

2. 典型例子：各部门按工资排序，从高到低编号

SELECT 
    employee_id, 
    department_id, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees;

结果：每个部门内部，工资最高的人 rn=1，次高 rn=2，等等。

===

3. 与 ROWNUM 的核心区别

====

三、其它

OVER 英文 在这里如何解析

====

PARTITION BY 对应的英文是什么

====

什么叫 分析函数，为什又叫窗口函数

====

为什么 ，WHERE ROWNUM > 1 永远不会返回任何行

----

---

====

Oracle 中 PARTITION BY 的作用是什么

====

四、使用

删除要求

一条 SQL 语句（不使用 WITH），基于表中 DDD 字段实际存在的月份，删除“按月份排序后第 13 个最新月份”之前的所有数据。如果不同月份总数不足 13 个，则不删除任何数据。
 

DELETE FROM your_table
WHERE DDD < (
    SELECT mon
    FROM (
        SELECT DISTINCT TRUNC(DDD, 'MM') AS mon,
               ROW_NUMBER() OVER (ORDER BY TRUNC(DDD, 'MM') DESC) AS rn
        FROM your_table
    )
    WHERE rn = 13
);

SQL 中使用了 TRUNC(DDD, 'MM')，这个函数会保留年份和月份（例如 2025-05-01、2024-12-01），因此按它降序排序时，年份不同的月份会自动按时间顺序排列

===========

===

删除效果举例

===

• 总数据条数：13 × 2 + 3 × 10 = 26 + 30 = 56

• 预期保留条数：26（No.1 ～ No.13）

• 预期删除条数：30（No.14 ～ No.16）

删除逻辑说明：
由于总共有 16 个不同月份，排序后最新的 13 个月（No.1~No.13）保留，剩下的 3 个月（No.14~No.16）全部删除。这与“保留最近 13 个有数据的月份”的规则一致。

====