第一步：我先用rownum
| 代碼如下 |
複製代碼 |
--分頁 row_number，不是rownum
--根據n_count從大到小排列，每頁3條
SELECT ROWNUM r,t.* FROM t_news t
WHERE ROWNUM<=3
ORDER BY t.n_count DESC
--問題：為什麼order by以後，行號是亂的？
SELECT ROWNUM r,t.* FROM t_news t
--原因：先分配了行號，再根據n_count排序 --所以必須排序，再產生行號
SELECT ROWNUM r,t.* FROM (
SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t --分頁
--err
SELECT ROWNUM r,t.* FROM (
SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t
WHERE r between 1 AND 3 --第1頁
SELECT ROWNUM r,t.* FROM (
SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t
WHERE ROWNUM between 1 AND 3 --第2頁
SELECT ROWNUM r,t.* FROM (
SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t
WHERE ROWNUM between 4 AND 6
--error: ROWNUM必須從1開始！
SELECT k.* FROM (
SELECT ROWNUM r,t.* FROM (
SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t
) k
WHERE r BETWEEN 4 AND 6 |
--麻煩，效率低！
*****第二步:我用row_number() over()函數
| 代碼如下 |
複製代碼 |
select t2.* from
(select t.*,row_number()over(order by t.n_count desc) orderNumber from t_news t order by t.n_count desc)t2 where orderNumber between 1and 3;
*****************************************************************************************************************************************88
SELECT * FROM (
SELECT t.*,row_number() over(ORDER BY n_count DESC) r
FROM t_news t
ORDER BY t.n_count DESC
) t
WHERE r BETWEEN 4 AND 6
|
--通用文法： 解析函數() over(partition by 欄位 order by 欄位)
