Oracle row_number()使用详解

1. 简介

在Oracle数据库中，row_number()是一个非常有用的函数，用于给查询结果中的每一行分配一个唯一的序号。本文将详细介绍row_number()函数的语法、用法以及示例代码，并解释如何使用该函数在Oracle数据库中进行分页查询。

2. row_number()函数语法

row_number()函数的语法如下：

ROW_NUMBER() OVER ([PARTITION BY column_list] ORDER BY column [ASC|DESC], ...)

其中，

• PARTITION BY是可选的，用于对结果集进行分组（按照指定的列进行分组）。
• ORDER BY用于指定结果集的排序方式，可以按照一个或多个列进行排序，ASC表示升序，DESC表示降序。

3. row_number()函数用法示例

3.1 基本用法

首先，我们创建一个示例表”employees”，包含以下列：id, name, department, salary。

CREATE TABLE employees (
    id NUMBER,
    name VARCHAR2(100),
    department VARCHAR2(100),
    salary NUMBER
);

INSERT INTO employees VALUES (1, 'John', 'HR', 5000);
INSERT INTO employees VALUES (2, 'Lisa', 'Finance', 6000);
INSERT INTO employees VALUES (3, 'David', 'IT', 5500);
INSERT INTO employees VALUES (4, 'Sophia', 'Finance', 7000);
INSERT INTO employees VALUES (5, 'Michael', 'HR', 4500);

现在，我们使用row_number()函数给每一行分配一个唯一的序号：

SELECT id, name, department, salary, 
       ROW_NUMBER() OVER (ORDER BY id) AS rownum
FROM employees;

该查询结果如下：

ID | NAME    | DEPARTMENT | SALARY | ROWNUM
-----------------------------------------------
1  | John    | HR         | 5000   | 1
2  | Lisa    | Finance    | 6000   | 2
3  | David   | IT         | 5500   | 3
4  | Sophia  | Finance    | 7000   | 4
5  | Michael | HR         | 4500   | 5

可以看到，row_number()函数给每一行分配了一个唯一的序号，并按照id进行升序排序。

3.2 使用PARTITION BY进行分组

如果我们想要按照部门对员工进行分组，并且在每个分组内部对员工按照薪水进行排序，可以使用PARTITION BY子句：

SELECT id, name, department, salary, 
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rownum
FROM employees;

该查询结果如下：

ID | NAME    | DEPARTMENT | SALARY | ROWNUM
-----------------------------------------------
2  | Lisa    | Finance    | 6000   | 1
4  | Sophia  | Finance    | 7000   | 2
1  | John    | HR         | 5000   | 1
5  | Michael | HR         | 4500   | 2
3  | David   | IT         | 5500   | 1

可以看到，row_number()函数在每个部门内部对员工按照薪水进行排序，并分配了唯一的序号。

3.3 结合分页查询

row_number()函数常常与分页查询结合使用。下面的示例演示如何使用row_number()函数进行分页查询：

SELECT id, name, department, salary
FROM (
    SELECT id, name, department, salary, 
           ROW_NUMBER() OVER (ORDER BY id) AS rownum
    FROM employees
)
WHERE rownum BETWEEN 2 AND 3;

该查询结果如下：

ID | NAME   | DEPARTMENT | SALARY
-----------------------------------
2  | Lisa   | Finance    | 6000
3  | David  | IT         | 5500

可以看到，该查询仅返回第2到第3行的结果。

4. 总结

本文介绍了Oracle数据库中row_number()函数的使用方法。row_number()函数可以为查询结果中的每一行分配一个唯一的序号，方便在数据库中进行分组、排序和分页查询。通过合理使用row_number()函数，可以更加灵活和高效地操作数据库中的数据。