Oracle数据库rownum和row_number的不同点

在Oracle数据库中，我们常常会用到rownum和row_number这两个关键字来操作行数据。它们虽然都可以用来对查询结果的行数进行限制，但它们有着不同的用法和功效。本文将详细讲解这两个关键字的区别，并结合实例进行说明。

rownum

rownum是Oracle数据库中内置的一个伪列，它在查询结果返回之后才会计算。rownum可以用来计算查询结果集中每一行的行号，但不能在where字句中使用和修改，因为它是在计算结果后才产生的。一般情况下，rownum的使用如下：

select * from table where rownum <= 10;

此语句的作用是查询table表中前10行数据。

示例1

我们假设有一个表employee，其中包含员工信息，例如姓名、年龄、性别等：

id	name	age	gender
1	Alice	25	Female
2	Bob	28	Male
3	Chris	30	Male
4	David	35	Male
5	Emily	23	Female
6	Frank	31	Male
7	Grace	27	Female
8	Helen	29	Female
9	Ida	24	Female
10	Jack	33	Male
11	Kevin	26	Male
12	Lucy	32	Female
13	Mary	28	Female
14	Nancy	25	Female
15	Olivia	29	Female
16	Peter	30	Male
17	Queenie	27	Female
18	Rose	31	Female
19	Steve	28	Male
20	Tom	32	Male

我们希望查询employee表中前5条年龄最小的员工信息，可以使用如下查询语句：

select * from (
  select * from employee order by age
) where rownum <= 5;

这个查询语句中，先通过子查询将employee表按年龄从小到大排序，然后再对查询结果使用rownum进行限制，只返回前5行。

结果如下：

id	name	age	gender
5	Emily	23	Female
9	Ida	24	Female
1	Alice	25	Female
14	Nancy	25	Female
7	Grace	27	Female

示例2

我们继续使用上面的employee表，假设现在有一个需求：查询年龄在25岁以上的前5个员工信息。如果直接使用rownum限制行数，会得到以下结果：

select * from employee where age >= 25 and rownum <= 5;

结果：

id	name	age	gender
1	Alice	25	Female

我们发现这个查询语句只返回了一行记录，不符合我们的要求。这是因为查询条件age >= 25和rownum <= 5在执行查询时是同时进行的，Oracle数据库返回了满足条件的第一行即结束。为了避免这种情况的发生，我们可以使用以下方式：

select * from (
  select * from employee where age >= 25 order by age
) where rownum <= 5;

在这个查询语句中，我们先对年龄大于等于25岁的员工进行筛选，然后按年龄从小到大排序，最后对结果使用rownum进行限制。这样就可以得到正确的结果了。

row_number

row_number是Oracle数据库中窗口函数中的一种，它可以用来计算查询结果集中每一行的行号。与rownum不同的是，row_number是在查询结果返回之前产生的，因此可以进行更为灵活的操作。一般情况下，row_number的使用如下：

select row_number() over (order by column_name) as row_num, *
from table;

此语句的作用是为查询结果集中的每一行添加一个序列号row_num，使其按指定列名进行排序。

示例1

我们仍然使用上面的employee表作为例子，现在需求变成了查询年龄在25岁以上的前5个员工信息，但是我们需要同时显示它们在所有员工中的排名。

select row_number() over (order by age) as row_num, e.*
from employee e
where age >= 25 and rownum <= 5;

这个查询语句中使用了row_number函数，并使用age来进行排序，同时仍然对结果集进行rownum限制。得到的结果如下：

row_num	id	name	age	gender
1	1	Alice	25	Female
2	14	Nancy	25	Female
3	7	Grace	27	Female
4	18	Rose	31	Female
5	12	Lucy	32	Female

我们可以看到，查询结果中不仅包含了员工信息，还将查询结果集中的每一行按年龄从小到大排序，并添加了一个序列号row_num，使得我们可以轻松得到每个员工在所有员工中的排名。

示例2

假设我们现在有一个需求：查询男性员工信息，并按年龄从小到大排序，在每个年龄段内按工资从高到低排序，最后给工资最高的男性员工输出"赚钱特别多"。我们可以使用如下SQL语句：

select 
  id, name, age, salary,
  rank() over (partition by age order by salary desc) as salary_rank
from employee
where gender = 'Male'
order by age, salary desc;

这个查询语句在查询结果中使用rank()函数，对员工按年龄分组，然后按工资从高到低进行排序，并添加一个salary_rank列，用来表示该员工在同龄男性员工中的收入排名。最后还对查询结果按年龄从小到大，再按工资从高到低进行排序。

为了输出"赚钱特别多"的男性员工，我们还需要在以上SQL语句的基础上进行扩展，可以看下面的SQL语句：

with male_salary_rank as (
  select 
    id, name, age, salary, 
    rank() over (partition by age order by salary desc) as salary_rank
  from employee
  where gender = 'Male'
)
select 
  id, name, age, salary, 
  case
    when age in (25, 30) and salary_rank = 1 then '赚钱特别多'
    else ''
  end as comment
from male_salary_rank
order by age, salary desc;

这个SQL语句先使用公共表达式将查询结果存放到male_salary_rank中，然后对male_salary_rank中的内容进行加工，使用case语句对年龄为25岁和30岁的员工进行特别处理，输出"赚钱特别多"的信息。最后再将结果按年龄从小到大，再按工资从高到低进行排序。

总结

rownum和row_number都可以用来计算查询结果集中每一行的行号，但它们有着不同的用法和功效。rownum是Oracle数据库中内置的一个伪列，它在查询结果返回之后才会计算；row_number是Oracle数据库中窗口函数中的一种，它可以用来计算查询结果集中每一行的行号，同时可以进行更为灵活的操作。可以根据具体需求，灵活选择使用这两个关键字来操作行数据。