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 筆 |
WHERE rn = 1
RANK() OVER (PARTITION BY a ORDER BY b DESC) AS rk
WHERE rk <= 3 -- 若第2名並列2筆,會取出4筆
DENSE_RANK() OVER (PARTITION BY a ORDER BY b DESC) AS drk
WHERE drk <= 3 -- 並列不跳號
PARTITION BY dept_id, job_id
ORDER BY hire_date DESC, salary DESC
PARTITION BY、ORDER BY 欄位有合適索引(如 INDEX ON (a, b DESC))。MATERIALIZE 提示子查詢物化,或分批處理。FETCH FIRST 3 ROWS WITH TIES 簡化語法(需配合 PARTITION BY 視圖)。此模式是「分組 Top-N」查詢的標準解法,廣泛應用於:每類商品銷售前三、每用戶最近 5 筆訂單、每部門最高薪資前三名等業務場景。