網頁

▼

2022/5/22

PosgreSQL 分頁查詢 paging query

PosgreSQL分頁(pagination)查詢的SQL如下。


以查詢employee資料表為例。

CREATE TABLE IF NOT EXISTS employee (
    id bigserial PRIMARY KEY,
    name varchar(60) UNIQUE NOT NULL,
    age integer,
    created_at timestamp NOT NULL,
    deleted BOOLEAN DEFAULT FALSE NOT NULL
);

使用LIMIT和OFFSET語法。以employee資料表為例。

SELECT * FROM employee
WHERE deleted = false
ORDER BY id
LIMIT 筆數 OFFSET (頁數-1)*筆數;

LIMIT限制顯示的查詢結果筆數；
OFFSET略過原始查詢結果從頭開始算後幾筆。值為0或null時省略。

頁數為所在頁數，例如第2頁。
筆數為一頁要顯示的筆數，例如一頁顯示10筆。

所以查詢第2頁，每頁顯示10筆的SQL如下。

SELECT * FROM employee
WHERE deleted = false
ORDER BY id
LIMIT 10 OFFSET (2-1)*10;


通常分頁查詢還需要總筆數來計算全部頁數，則可使用下面SQL。

WITH t AS (
    SELECT *
    FROM employee
    WHERE deleted = false
)
SELECT *
FROM (
    TABLE t
    ORDER BY id
    LIMIT 筆數
    OFFSET (頁數-1)*筆數
) p
RIGHT JOIN (
    SELECT count(*) FROM t
) c (total) ON true;

沒有留言:

張貼留言