oracle 查询时给数据加序列

Oracle 查询时给数据加序列的详细教程

在数据库管理和数据处理中，有时需要对查询结果进行排序或添加序号，以便更好地展示和分析数据，在 Oracle 数据库中，可以通过多种方式在查询时给数据添加序列，以下将详细介绍几种常见的方法。

一、使用 ROWNUM 伪列

ROWNUM 是 Oracle 提供的一个伪列，它为查询结果集中的每一行分配一个唯一的行号，从 1 开始递增，但需要注意的是，ROWNUM 是在查询执行过程中动态生成的，它会在返回结果集之前被赋值，因此在某些复杂查询场景下可能会出现一些不符合预期的行为。

示例

假设有一个名为employees 的表，包含以下列：employee_id（员工 ID）、first_name（名）和last_name（姓），我们想要查询所有员工的信息，并给每一行添加一个序号。

SELECT ROWNUM AS SEQ, employee_id, first_name, last_name
FROM employees;

上述查询将为查询结果中的每行数据添加一个从 1 开始的序号，结果如下表所示：

当使用ORDER BY 子句对查询结果进行排序时，ROWNUM 的行为可能会变得不稳定。

SELECT ROWNUM AS SEQ, employee_id, first_name, last_name
FROM employees
ORDER BY last_name;

由于ROWNUM 是在排序之前生成的，所以可能无法得到按照姓氏排序后的正确序号。

二、使用 ROW_NUMBER() 函数

ROW_NUMBER() 函数是 Oracle 提供的一个分析函数，它可以为查询结果集中的每一行分配一个唯一的行号，并且可以指定排序规则，从而避免了ROWNUM 在排序时的不稳定问题。

示例

同样以employees 表为例，如果我们想要按照姓氏的字母顺序给员工信息添加序号，可以使用以下查询：

SELECT ROW_NUMBER() OVER (ORDER BY last_name) AS SEQ, employee_id, first_name, last_name
FROM employees;

上述查询将按照姓氏的字母顺序为每行数据添加序号，结果如下表所示：

三、使用 RANK() 或 DENSE_RANK() 函数

RANK() 和DENSE_RANK() 函数也是分析函数，它们与ROW_NUMBER() 类似，但在处理重复值时有所不同。RANK() 会为重复值分配相同的序号，并跳过下一个序号；而DENSE_RANK() 也会为重复值分配相同的序号，但不会跳过下一个序号。

示例

假设我们有一个sales 表，包含以下列：sale_id（销售记录 ID）、product_name（产品名称）和sale_amount（销售金额），我们想要根据销售金额对销售记录进行排名，并为每条记录添加序号。

使用RANK() 函数：

SELECT RANK() OVER (ORDER BY sale_amount DESC) AS SEQ, sale_id, product_name, sale_amount
FROM sales;

如果存在销售金额相同的记录，它们将获得相同的序号，如下表所示：

使用DENSE_RANK() 函数：

SELECT DENSE_RANK() OVER (ORDER BY sale_amount DESC) AS SEQ, sale_id, product_name, sale_amount
FROM sales;

对于销售金额相同的记录，它们将获得相同的序号，且不会跳过下一个序号，如下表所示：

相关问题与解答

问题 1：ROWNUM 和ROW_NUMBER() 在功能上有什么主要区别？

答：ROWNUM 是一个伪列，它在查询执行过程中动态生成行号，从 1 开始递增，但它在排序时可能会出现不稳定的情况，而ROW_NUMBER() 是一个分析函数，它可以指定排序规则来为每行数据分配唯一的行号，能够更精确地控制序号的生成，尤其是在需要按照特定顺序排序的场景下。

问题 2：在什么情况下适合使用RANK() 函数而不是DENSE_RANK() 函数？

答：当需要对数据进行排名，并且在存在相同值的情况下，希望跳过下一个序号时，适合使用RANK() 函数，在一些竞赛排名场景中，如果多名选手成绩相同，他们获得相同的名次，但下一名选手的名次会相应往后排，而DENSE_RANK() 函数则适用于不希望跳过下一个序号的情况，即使存在相同值，序号也会连续排列，在统计产品的销售排名时，即使某些产品销售数量相同，也希望它们的排名是连续的，以便更准确地反映整体的销售情况分布。