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;