Oracle 序号函数（ROW_NUMBER, RANK, DENSE_RANK）

在 Oracle 数据库中，序号函数用于为查询结果集中的每一行分配一个唯一的序号或排名。这些函数在处理需要排序和排名的数据时非常有用。以下是三种主要的序号函数：ROW_NUMBER(), RANK(), 和 DENSE_RANK()。

1. ROW_NUMBER()

功能描述

ROW_NUMBER() 函数为查询结果集中的每一行分配一个唯一的连续整数。即使两行具有相同的值，它们也会被赋予不同的序号。

语法

ROW_NUMBER() OVER ([PARTITION BY partition_expression] ORDER BY sort_expression [ASC|DESC])

PARTITION BY 子句（可选）：将结果集划分为分区，并在每个分区内独立计算行号。
ORDER BY 子句：指定用于排序的列。

示例

假设有一个名为 employees 的表，包含以下数据：

employee_id | department_id | salary ------------|---------------|------- 1 | 10 | 5000 2 | 20 | 6000 3 | 10 | 5000 4 | 30 | 7000

使用 ROW_NUMBER() 为每个部门的员工按工资排序并分配序号：

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

结果：

employee_id | department_id | salary | row_num ------------|---------------|--------|--------- 2 | 20 | 6000 | 1 4 | 30 | 7000 | 1 1 | 10 | 5000 | 1 3 | 10 | 5000 | 2

2. RANK()

功能描述

RANK() 函数根据指定的排序规则为每一行分配一个排名。如果两行具有相同的值，则它们会被赋予相同的排名，并且后续行的排名会跳过相应的数量。

语法

RANK() OVER ([PARTITION BY partition_expression] ORDER BY sort_expression [ASC|DESC])

示例

继续使用上面的 employees 表：

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

结果：

employee_id | department_id | salary | rank_num ------------|---------------|--------|---------- 2 | 20 | 6000 | 1 4 | 30 | 7000 | 1 1 | 10 | 5000 | 1 3 | 10 | 5000 | 1

注意：尽管部门 10 有两个员工的工资相同，它们的排名都是 1，但后续没有排名 2，下一个排名直接跳到 2 以外的数字（如果有更多不同工资的行）。

3. DENSE_RANK()

功能描述

DENSE_RANK() 函数类似于 RANK()，但它不会跳过后续的排名编号。即使有相同的值，后续行的排名也会紧接着前一个排名。

语法

DENSE_RANK() OVER ([PARTITION BY partition_expression] ORDER BY sort_expression [ASC|DESC])

示例

继续使用上面的 employees 表：

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

结果：

employee_id | department_id | salary | dense_rank_num ------------|---------------|--------|---------------- 2 | 20 | 6000 | 1 4 | 30 | 7000 | 1 1 | 10 | 5000 | 1 3 | 10 | 5000 | 1

如果添加更多不同工资的员工到表中，DENSE_RANK() 会继续按顺序分配排名，不会有任何跳跃。

希望这份文档能帮助你理解 Oracle 数据库中的序号函数。如果你有任何其他问题或需要进一步的信息，请随时告诉我！