常見數(shù)據(jù)庫(kù)中SQL分頁(yè)語法整理大全(附示例)
MySQL分頁(yè)查詢
在MySQL中，分頁(yè)查詢通常使用 LIMIT 關(guān)鍵字來實(shí)現(xiàn)。
LIMIT [offset,] rows 其中，offset表示偏移量（從結(jié)果集的第幾行開始返回，默認(rèn)從 0 開始計(jì)數(shù)），rows表示要返回的行數(shù)。
常規(guī)版
# 查詢第一頁(yè)（假設(shè)每頁(yè)顯示 10 條記錄）： SELECT * FROM users LIMIT 0, 10; #這里0是偏移量，表示從第 1 行（偏移量為 0）開始，10是要返回的行數(shù)。 #查詢第二頁(yè)： SELECT * FROM users LIMIT 10, 10; #此時(shí)偏移量為10，即跳過前面 10 行，然后返回 10 行。
升級(jí)版
更靈活的分頁(yè)查詢如果你需要更靈活地處理分頁(yè)，可以使用變量來動(dòng)態(tài)設(shè)置 offset 和 rows：
SET @page_size := 10;
SET @page_number := 2;
SET @offset := (@page_size * (@page_number - 1));
SELECT *
FROM employees
LIMIT @offset, @page_size;
# 在存儲(chǔ)過程中使用 LIMIT 進(jìn)行分頁(yè)查詢，以下是一個(gè)簡(jiǎn)單的存儲(chǔ)過程示例，用于實(shí)現(xiàn)分頁(yè)查詢：
DELIMITER $$
CREATE PROCEDURE get_page(IN page_number INT, IN page_size INT)
BEGIN
SET @offset = (page_number - 1) * page_size;
SET @limit = page_size;
SET @sql = CONCAT('SELECT * FROM your_table LIMIT ', @offset, ', ', @limit);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
使用 JOIN 或子查詢進(jìn)行分頁(yè)有時(shí)候，你可能需要在復(fù)雜的查詢中進(jìn)行分頁(yè)，例如包含 JOIN 或子查詢：
SELECT e.*
FROM employees e
JOIN (
SELECT employee_id
FROM employees
ORDER BY hire_date
LIMIT @offset, @page_size
) as sub_query ON e.employee_id = sub_query.employee_id;
MySQL 8.0 引入了窗口函數(shù)，可以使用 ROW_NUMBER() 來實(shí)現(xiàn)分頁(yè)：
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY hire_date) as row_num
FROM employees
) as paginated_results
WHERE row_num BETWEEN @offset + 1 AND @offset + @page_size;
Oracle分頁(yè)查詢
在Oracle數(shù)據(jù)庫(kù)中，分頁(yè)查詢通常使用ROWNUM偽列或者數(shù)據(jù)庫(kù)12c及更高版本中的FETCH FIRST和OFFSET子句來實(shí)現(xiàn)。
常規(guī)版
在Oracle 12c之前的版本，分頁(yè)查詢通常依賴于ROWNUM偽列。ROWNUM為結(jié)果集中的每一行分配一個(gè)唯一的行號(hào)。
SELECT * FROM (
SELECT temp.*, ROWNUM rnum FROM (
SELECT * FROM your_table WHERE conditions ORDER BY some_column
) temp WHERE ROWNUM <= :upper_bound
) WHERE rnum > :lower_bound;
# :upper_bound是上界值，通常是(頁(yè)碼 * 每頁(yè)顯示的行數(shù))。
# :lower_bound是下界值，通常是(頁(yè)碼 - 1) * 每頁(yè)顯示的行數(shù) + 1。
# 假設(shè)你有一個(gè)名為employees的表，你想查詢第2頁(yè)的數(shù)據(jù)，每頁(yè)顯示10行數(shù)據(jù)。
SELECT * FROM (
SELECT temp.*, ROWNUM rnum FROM (
SELECT * FROM employees ORDER BY employee_id
) temp WHERE ROWNUM <= 20
) WHERE rnum > 10;
Oracle 12c及更高版本使用OFFSET和FETCH進(jìn)行分頁(yè)
SELECT * FROM your_table WHERE conditions ORDER BY some_column OFFSET :lower_bound ROWS FETCH NEXT :page_size ROWS ONLY; # :lower_bound是分頁(yè)的起始行，通常是(頁(yè)碼 - 1) * 每頁(yè)顯示的行數(shù)。 # :page_size是每頁(yè)顯示的行數(shù)。 # 假設(shè)你有一個(gè)名為employees的表，你想查詢第2頁(yè)的數(shù)據(jù)，每頁(yè)顯示10行數(shù)據(jù)。 SELECT * FROM employees ORDER BY employee_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; # 這里，OFFSET 10 ROWS跳過前10行，F(xiàn)ETCH NEXT 10 ROWS ONLY則返回接下來的10行。
升級(jí)版
動(dòng)態(tài)分頁(yè)查詢有時(shí)候我們可能需要根據(jù)用戶輸入或者業(yè)務(wù)邏輯動(dòng)態(tài)地確定每頁(yè)顯示的記錄數(shù)和頁(yè)碼。
例如，假設(shè)我們有一個(gè)存儲(chǔ)過程，接受兩個(gè)參數(shù)：p_page_number（頁(yè)碼）和p_page_size（每頁(yè)記錄數(shù)）。
在這個(gè)存儲(chǔ)過程中，首先計(jì)算偏移量v_offset，然后在內(nèi)部子查詢中使用分析函數(shù)ROW_NUMBER()對(duì)表中的數(shù)據(jù)按照some_column（根據(jù)實(shí)際需求替換為排序依據(jù)的列）進(jìn)行排序和編號(hào)。接著在中層子查詢中根據(jù)計(jì)算得到的偏移量和每頁(yè)記錄數(shù)限制ROWNUM，最后在外層子查詢中根據(jù)rn（行號(hào)）篩選出真正需要的頁(yè)面數(shù)據(jù)。
CREATE OR REPLACE PROCEDURE dynamic_paging(p_page_number IN NUMBER, p_page_size IN NUMBER) AS
v_offset NUMBER := (p_page_number - 1) * p_page_size;
BEGIN
-- 使用分析函數(shù)和ROWNUM進(jìn)行分頁(yè)
SELECT *
FROM (
SELECT t.*, ROWNUM rn
FROM (
SELECT col1, col2, col3, -- 這里列出實(shí)際表中的列
-- 使用分析函數(shù)（例如ROW_NUMBER）對(duì)數(shù)據(jù)進(jìn)行排序和編號(hào)
ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
FROM your_table
) t
WHERE ROWNUM <= v_offset + p_page_size
)
WHERE rn > v_offset;
END;
多表連接與分頁(yè)結(jié)合當(dāng)查詢涉及多個(gè)表連接時(shí)，分頁(yè)操作需要考慮連接結(jié)果的順序和數(shù)量。
假設(shè)有table1和table2兩個(gè)表，我們要查詢連接后的結(jié)果并進(jìn)行分頁(yè)。
這里先進(jìn)行table1和table2的連接操作，然后使用ORDER BY對(duì)連接結(jié)果進(jìn)行排序，接著在內(nèi)部子查詢中使用ROWNUM進(jìn)行初步的分頁(yè)篩選，最后在外層子查詢中根據(jù)行號(hào)rn進(jìn)行調(diào)整以得到正確的第一頁(yè)數(shù)據(jù)。對(duì)于多表連接的分頁(yè)查詢，確保連接的正確性以及排序的合理性是很重要的，這樣才能得到準(zhǔn)確的分頁(yè)結(jié)果。
SELECT *
FROM (
SELECT t.*, ROWNUM rn
FROM (
SELECT col1, col2, col3 -- 這里列出連接后的列
FROM table1
JOIN table2 ON table1.key = table2.key
ORDER BY some_column
) t
WHERE ROWNUM <= 10
)
WHERE rn > 0;
分區(qū)表的分頁(yè)優(yōu)勢(shì)如果數(shù)據(jù)存儲(chǔ)在分區(qū)表中，可以利用分區(qū)的特性更高效地進(jìn)行分頁(yè)查詢。
假設(shè)我們有一個(gè)按照日期分區(qū)的表partitioned_table，要查詢某個(gè)日期分區(qū)內(nèi)的數(shù)據(jù)并進(jìn)行分頁(yè)。
通過指定分區(qū)名，可以直接在該分區(qū)內(nèi)進(jìn)行分頁(yè)查詢，減少了查詢的數(shù)據(jù)量，提高了查詢效率，尤其是在處理大型表時(shí)這種優(yōu)勢(shì)更加明顯。
SELECT *
FROM (
SELECT t.*, ROWNUM rn
FROM (
SELECT col1, col2, col3
FROM partitioned_table PARTITION (partition_name) -- 替換為實(shí)際分區(qū)名
ORDER BY some_column
) t
WHERE ROWNUM <= 10
)
WHERE rn > 0;
PostgreSQL分頁(yè)查詢
在PostgreSQL中，分頁(yè)查詢通常使用 LIMIT 和 OFFSET 子句來實(shí)現(xiàn)。這兩個(gè)子句可以限制查詢結(jié)果的數(shù)量，并指定返回結(jié)果的起始點(diǎn)。
常規(guī)版
使用 LIMIT 和 OFFSET 關(guān)鍵字
# column_name是用于排序的列（如果不指定排序順序，分頁(yè)結(jié)果可能會(huì)不穩(wěn)定）， # count 是要返回的行數(shù)（即每頁(yè)的行數(shù)）， # offset_value是偏移量（表示從結(jié)果集的第幾行開始返回）。 SELECT * FROM table_name WHERE condition ORDER BY column_name LIMIT count OFFSET offset_value; # 查詢第一頁(yè)（假設(shè)每頁(yè)顯示 10 條記錄） # 這里查詢users表，按照user_id排序，返回前 10 條記錄（偏移量為 0 表示從第一行開始）。 SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 0; # 查詢第二頁(yè) SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 10;
使用 FETCH 和 OFFSET（PostgreSQL 8.4 及以上版本支持 FETCH）
SELECT * FROM your_table ORDER BY some_column OFFSET offset_value FETCH FIRST num_rows ROWS ONLY; SELECT * FROM users ORDER BY user_id OFFSET 0 FETCH FIRST 10 ROWS ONLY; SELECT * FROM users ORDER BY user_id OFFSET 10 FETCH FIRST 10 ROWS ONLY;
升級(jí)版
更靈活的分頁(yè)查詢
SET @page_size = 10;
SET @page_number = 2;
SET @offset = (@page_size * (@page_number - 1));
SELECT *
FROM employees
ORDER BY employee_id
LIMIT @page_size OFFSET @offset;
# 在這個(gè)函數(shù)中，根據(jù)傳入的頁(yè)碼p_page_number和每頁(yè)大小p_page_size構(gòu)建動(dòng)態(tài) SQL 語句。
#然后使用RETURN QUERY EXECUTE執(zhí)行動(dòng)態(tài) SQL 并返回結(jié)果。這種方式使得分頁(yè)查詢更加靈活，能夠適應(yīng)不同的需求。
CREATE OR REPLACE FUNCTION dynamic_paging(p_page_number INT, p_page_size INT)
RETURNS SETOF your_table_type AS
$BODY$
DECLARE
v_sql TEXT;
BEGIN
v_sql := format('SELECT * FROM your_table ORDER BY some_column LIMIT %s OFFSET %s', p_page_size, (p_page_number - 1) * p_page_size);
RETURN QUERY EXECUTE v_sql;
END;
$BODY$
LANGUAGE plpgsql;多表連接后的分頁(yè)
# 當(dāng)查詢涉及多個(gè)表的連接時(shí)，分頁(yè)操作需要考慮連接結(jié)果集的順序和結(jié)構(gòu)。
# 例如，假設(shè)有三個(gè)表table1、table2和table3，要對(duì)它們連接后的結(jié)果進(jìn)行分頁(yè)：
SELECT *
FROM (
SELECT sub.*, ROWNUM AS row_num
FROM (
SELECT t1.col1, t2.col2, t3.col3
FROM table1 t1
JOIN table2 t2 ON t1.key = t2.key
JOIN table3 t3 ON t2.other_key = t3.other_key
ORDER BY t1.some_column
) sub
)
WHERE row_num BETWEEN start_row AND end_row;
#首先在內(nèi)部子查詢中進(jìn)行多表連接并按照table1中的some_column排序。
#然后在中層子查詢中使用ROWNUM（這里ROWNUM類似 Oracle 中的概念，
#在 PostgreSQL 中是自定義的行號(hào)）為結(jié)果集編號(hào)。
#最后在外層子查詢中根據(jù)計(jì)算出的開始行start_row和結(jié)束行
#end_row（可以根據(jù)頁(yè)碼和每頁(yè)大小計(jì)算得出）進(jìn)行分頁(yè)篩選。
使用窗口函數(shù)進(jìn)行分頁(yè)PostgreSQL 8.4及以上版本支持窗口函數(shù)，可以使用 row_number() 窗口函數(shù)來實(shí)現(xiàn)更復(fù)雜的分頁(yè)邏輯：
WITH paginated AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
FROM employees
)
SELECT *
FROM paginated
WHERE rn BETWEEN 11 AND 20;
#窗口函數(shù)可以在不改變結(jié)果集行數(shù)的情況下對(duì)每一行進(jìn)行計(jì)算，這在復(fù)雜的分頁(yè)場(chǎng)景中很有用。
#例如，假設(shè)要對(duì)一個(gè)包含員工信息的表employees進(jìn)行分頁(yè)，并且在分頁(yè)結(jié)果中顯示每個(gè)員工在部門內(nèi)的排名：
SELECT department, employee_name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM employees
ORDER BY department, rank_in_department
LIMIT page_size OFFSET offset;
# 這里使用ROW_NUMBER()窗口函數(shù)計(jì)算每個(gè)員工在其所屬部門內(nèi)按照薪資降序排列的排名。
# 然后按照部門和部門內(nèi)排名進(jìn)行排序，最后進(jìn)行分頁(yè)操作。這種方式可以在分頁(yè)結(jié)果中提供更多的信息和分析價(jià)值。
SQL Server分頁(yè)查詢
在 SQL Server 中，分頁(yè)查詢可以通過使用 OFFSET 和 FETCH 子句來實(shí)現(xiàn)。這些子句在 SQL Server 2012 及更高版本中被引入，提供了一種簡(jiǎn)單且直觀的方式來進(jìn)行分頁(yè)。
常規(guī)版
使用OFFSET - FETCH子句（SQL Server 2012 及以上版本）
SELECT * FROM your_table ORDER BY some_column OFFSET offset_rows ROWS FETCH NEXT fetch_rows ROWS ONLY; # 其中，your_table是要查詢的表名， # some_column是用于排序的列（如果不指定排序順序，分頁(yè)結(jié)果可能會(huì)不穩(wěn)定）， # offset_rows是偏移量（表示從結(jié)果集的第幾行開始返回）， # fetch_rows是要返回的行數(shù)（即每頁(yè)的行數(shù)）。 # 查詢第一頁(yè)（假設(shè)每頁(yè)顯示 10 條記錄） SELECT * FROM users ORDER BY user_id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; # 查詢第二頁(yè) SELECT * FROM users ORDER BY user_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
使用TOP關(guān)鍵字結(jié)合子查詢（適用于舊版本）
# 查詢第一頁(yè)（假設(shè)每頁(yè)顯示 10 條記錄）
SELECT TOP 10 * FROM your_table ORDER BY some_column;
# 查詢第二頁(yè)（假設(shè)每頁(yè)顯示 10 條記錄）
# 這個(gè)方法相對(duì)復(fù)雜一些，在子查詢中獲取前 20 條記錄，然后通過外部查詢排除前 10 條記錄來得到第二頁(yè)的數(shù)據(jù)。
SELECT * FROM (
SELECT TOP 20 * FROM your_table ORDER BY some_column
) AS subquery
WHERE NOT EXISTS (SELECT TOP 10 * FROM your_table ORDER BY some_column)
ORDER BY some_column;
升級(jí)版
使用動(dòng)態(tài) SQL 實(shí)現(xiàn)靈活分頁(yè)
# 例如，創(chuàng)建一個(gè)存儲(chǔ)過程，接受頁(yè)碼@pageNumber和每頁(yè)行數(shù)@pageSize作為參數(shù)：
CREATE PROCEDURE DynamicPaging
@pageNumber INT,
@pageSize INT
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
FROM your_table
) AS subquery
WHERE row_num BETWEEN ' + CAST((@pageNumber - 1) * @pageSize + 1 AS NVARCHAR(10)) + ' AND ' + CAST(@pageNumber * @pageSize AS NVARCHAR(10));
EXEC sp_executesql @sql;
END;
# 在這個(gè)存儲(chǔ)過程中，首先構(gòu)建動(dòng)態(tài) SQL 語句。使用ROW_NUMBER()函數(shù)為結(jié)果集中的每一行分配一個(gè)行號(hào)（按照some_column排序），
# 然后根據(jù)傳入的頁(yè)碼和每頁(yè)行數(shù)計(jì)算出要獲取的行號(hào)范圍，最后通過sp_executesql執(zhí)行動(dòng)態(tài) SQL 語句來實(shí)現(xiàn)分頁(yè)查詢。
多表連接后的分頁(yè)
# 例如，假設(shè)有表table1和table2，要對(duì)它們連接后的結(jié)果進(jìn)行分頁(yè)：
SELECT *
FROM (
SELECT sub.*, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
FROM (
SELECT t1.col1, t2.col2
FROM table1 t1
JOIN table2 t2 ON t1.key = t2.key
ORDER BY some_column
) AS sub
) AS final
WHERE row_num BETWEEN start_row AND end_row;
# 首先在內(nèi)部子查詢中進(jìn)行table1和table2的連接，并按照some_column排序。
#然后在中層子查詢中使用ROW_NUMBER()函數(shù)為連接后的結(jié)果集分配行號(hào)。
#最后在外層子查詢中根據(jù)計(jì)算出的起始行start_row和結(jié)束行
#end_row（可以根據(jù)頁(yè)碼和每頁(yè)行數(shù)計(jì)算得出）進(jìn)行分頁(yè)篩選。
與窗口函數(shù)結(jié)合的分頁(yè)查詢
# 例如，要查詢員工表employees中的數(shù)據(jù)并進(jìn)行分頁(yè)，同時(shí)顯示每個(gè)部門內(nèi)員工的排名：
SELECT department, employee_name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM employees
ORDER BY department, rank_in_department
OFFSET offset_rows ROWS FETCH NEXT fetch_rows ROWS ONLY;
#這里使用ROW_NUMBER()窗口函數(shù)在每個(gè)部門內(nèi)按照薪資降序?yàn)閱T工分配排名。
#然后按照部門和部門內(nèi)排名進(jìn)行排序，最后再進(jìn)行分頁(yè)操作。這種方式可以在分頁(yè)結(jié)果中提供更詳細(xì)的部門內(nèi)員工信息。總結(jié)
到此這篇關(guān)于常見數(shù)據(jù)庫(kù)中SQL分頁(yè)語法整理大全的文章就介紹到這了,更多相關(guān)數(shù)據(jù)庫(kù)SQL分頁(yè)語法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
SQL實(shí)現(xiàn)數(shù)據(jù)過濾流程詳解
這篇文章主要介紹了SQL實(shí)現(xiàn)數(shù)據(jù)過濾流程，當(dāng)我們?cè)赟QL中查詢數(shù)據(jù)時(shí)，肯定是有一些數(shù)據(jù)是我們不需要的，所以我們此時(shí)就要對(duì)數(shù)據(jù)進(jìn)行過濾，以篩選出我們僅需要的數(shù)據(jù)2023-01-01
詳解MYSQL的備份還原(PHP實(shí)現(xiàn))
本篇文章是對(duì)MYSQL的備份還原進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
mysql之validate_password_policy的使用
這篇文章主要介紹了mysql之validate_password_policy的使用，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-05-05
java實(shí)現(xiàn)插入mysql二進(jìn)制文件，blob類型，遇到問題及解決辦法
mysql插入二進(jìn)制文件，blob類型，遇到問題及解決辦法2009-06-06
本文主要介紹了mysql 查看表大小的方法實(shí)踐，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
Mysql查詢?nèi)掌趖imestamp格式的數(shù)據(jù)實(shí)現(xiàn)
本文主要介紹了Mysql查詢?nèi)掌趖imestamp格式的數(shù)據(jù)實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
這篇文章主要介紹了Mysql如何查看表的索引問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
本文介紹下，在mysql數(shù)據(jù)庫(kù)中，有關(guān)case when語句的用法，介紹了case when語句的基礎(chǔ)知識(shí)，并提供了相關(guān)實(shí)例，供大家學(xué)習(xí)參考，有需要的朋友不要錯(cuò)過2021-08-08
MySQL誤刪數(shù)據(jù)庫(kù)造成了數(shù)據(jù)的丟失,這是非常尷尬的,下面這篇文章主要給大家介紹了關(guān)于MySQL恢復(fù)誤刪數(shù)據(jù)的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
mysql中怎么合并兩個(gè)字段為一個(gè)字段呢？試了好多方法，結(jié)果還是不是我想要的2012-08-08