oracle 的ROWNUM 和 ROW_NUMBER，以及分组排序

Oracle ROWNUM vs Oracle ROW_NUMBER

ROWNUM 是伪列，并没有实际意义。在ORDER BY执行之前，oracle给每一行查询结果赋予一个编号。
ROW_NUMBER 是oracle提供的函数，通过参数为查询结果编号。

区别

• ROWNUM 是伪列，没有参数。ROW_NUMBER 是分析函数，有参数。
• ROWNUM 在ORDER BY之前赋予查询结果。ROW_NUMBER 是 column 计算的一部分。
• ROWNUM 是唯一的。ROW_NUMBER 可以重复。

代码例子

SELECT ROWNUM, first_name, last_name, address_state
FROM student;

SELECT first_name, last_name, address_state,
ROW_NUMBER() OVER (PARTITION BY address_state ORDER BY last_name) AS row_number_value
FROM student;

ROW_NUMBER 分组排序

需求： 学生按照地域分组，每个地域选取名字排序的前20个。
sql：

select * from (
    SELECT first_name, last_name, address_state,
    ROW_NUMBER() OVER (PARTITION BY address_state ORDER BY last_name) AS row_number_value
    FROM student
    ) t
where t.row_number_value <= 20

ROWNUM limit

需求：
按照last_name排序，取名字排序的前五名。

错误的方法：

SELECT ROWNUM, first_name, last_name, address_state
FROM student
WHERE ROWNUM <= 5
ORDER BY last_name;

这个sql是先给每一行查询结果赋予ROWNUM，然后通过ROWNUM <=5 截取前五条数据。然后再对前五条数据进行排序。

下面是正确的实现方法：

SELECT ROWNUM, first_name, last_name, address_state
FROM (
  SELECT first_name, last_name, address_state
  FROM student
  ORDER BY last_name
) sub
WHERE ROWNUM <= 5;