萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 分組排序取 Top-N:ROW_NUMBER() OVER (PARTITION BY) 經典模式

核心 SQL 模式

SELECT *
FROM (
  SELECT t.b,
         t.a,
         ROW_NUMBER() OVER (PARTITION BY t.a ORDER BY t.b DESC) AS rn
  FROM dual t
)
WHERE rn <= 3;

關鍵字解析

子句作用
PARTITION BY t.a按欄位 A 分組,組內獨立編號
ORDER BY t.b DESC組內按欄位 B 降序排列
ROW_NUMBER()生成 1,2,3... 不重複連續序號
WHERE rn <= 3取每組前 3 筆

變體與擴展

1. 取每組第一筆(去重/最新記錄)

WHERE rn = 1

2. 使用 RANK() 允許並列

RANK() OVER (PARTITION BY a ORDER BY b DESC) AS rk
WHERE rk <= 3  -- 若第2名並列2筆,會取出4筆

3. 使用 DENSE_RANK() 連續排名

DENSE_RANK() OVER (PARTITION BY a ORDER BY b DESC) AS drk
WHERE drk <= 3  -- 並列不跳號

4. 多欄位分組/排序

PARTITION BY dept_id, job_id
ORDER BY hire_date DESC, salary DESC

效能提示

  • 確保 PARTITION BY、ORDER BY 欄位有合適索引(如 INDEX ON (a, b DESC))。
  • 大數據量考慮 MATERIALIZE 提示子查詢物化,或分批處理。
  • 12c+ 可用 FETCH FIRST 3 ROWS WITH TIES 簡化語法(需配合 PARTITION BY 視圖)。

此模式是「分組 Top-N」查詢的標準解法,廣泛應用於:每類商品銷售前三、每用戶最近 5 筆訂單、每部門最高薪資前三名等業務場景。

copyright © 萬盛學電腦網 all rights reserved