在Oracle中为查询结果添加序号

在实际开发中，我们经常需要在查询结果为每行数据添加一个序号。这个序号可以用于排序、分页或者作为数据的唯一标识。Oracle数据库提供了几种方法来实现这个功能。

方法一：使用ROWNUM伪列

ROWNUM是Oracle特有的伪列，它返回当前行在结果集中的行号。需要注意的是，ROWNUM是在数据被检索出来之后才分配的，所以直接使用ROWNUM可能会遇到一些问题。

-- 基本用法
SELECT ROWNUM AS row_num, employee_id, first_name, last_name
FROM employees
WHERE department_id = 50;

-- 结合ORDER BY使用时需要注意
-- 以下写法可能无法得到预期结果
SELECT ROWNUM AS row_num, employee_id, first_name, last_name
FROM employees
WHERE department_id = 50
ORDER BY salary DESC;

-- 正确的写法应该使用子查询
SELECT ROWNUM AS row_num, t.*
FROM (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = 50
    ORDER BY salary DESC
) t;

方法二：使用ROW_NUMBER()分析函数

ROW_NUMBER()是Oracle的分析函数之一，它为每一行分配一个唯一的数字。与ROWNUM不同，ROW_NUMBER()可以在ORDER BY子句之后执行，因此更适合用于需要排序的场景。

-- 基本用法
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       employee_id, 
       first_name, 
       last_name, 
       salary
FROM employees
WHERE department_id = 50;

-- 按部门分组并编号
SELECT department_id,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_row_num,
       employee_id, 
       first_name, 
       last_name, 
       salary
FROM employees;

方法三：使用RANK()和DENSE_RANK()函数

除了ROW_NUMBER()，Oracle还提供了RANK()和DENSE_RANK()函数，它们也可以为行分配序号，但在处理相同值时行为不同。

-- RANK()函数会在相同值处产生跳跃
SELECT RANK() OVER (ORDER BY salary DESC) AS rank_num,
       employee_id, 
       first_name, 
       last_name, 
       salary
FROM employees
WHERE department_id = 50;

-- DENSE_RANK()函数不会产生跳跃
SELECT DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num,
       employee_id, 
       first_name, 
       last_name, 
       salary
FROM employees
WHERE department_id = 50;

三种方法的比较

实际应用示例

以下是一个实际应用的例子，展示如何为员工表添加序号并实现分页功能：

-- 分页查询，每页10条记录，查询第2页
SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num,
           employee_id, 
           first_name, 
           last_name, 
           hire_date
    FROM employees
)
WHERE row_num BETWEEN 11 AND 20;

在这个例子中，我们首先使用ROW_NUMBER()函数为所有员工按入职日期排序并编号，然后在外部查询中通过WHERE条件筛选出指定范围的行，实现了分页功能。

注意事项

• ROWNUM是在数据检索过程中分配的，而ROW_NUMBER()是在排序后分配的
• 在使用ROWNUM时，如果需要排序，必须使用子查询
• ROW_NUMBER()、RANK()和DENSE_RANK()都是分析函数，只能在SELECT列表或ORDER BY子句中使用
• 这些方法在不同的Oracle版本中可能会有性能差异，需要根据实际情况选择

根据具体需求选择合适的方法为查询结果添加序号，可以使我们的SQL查询更加灵活和高效。

Oracle添加序号ROWNUM伪列ROW_NUMBER函数RANK_DENSE_RANK函数分页查询