SQL實現(xiàn)分頁查詢方法總結(jié)
開發(fā)過程中經(jīng)常遇到分頁的需求,今天在此總結(jié)一下吧。
簡單說來方法有兩種,一種在源上控制,一種在端上控制。源上控制把分頁邏輯放在SQL層;端上控制一次性獲取所有數(shù)據(jù),把分頁邏輯放在UI上(如GridView)。顯然,端上控制開發(fā)難度低,適于小規(guī)模數(shù)據(jù),但數(shù)據(jù)量增大時性能和IO消耗無法接受;源上控制在性能和開發(fā)難度上較為平衡,適應(yīng)大多數(shù)業(yè)務(wù)場景;除此之外,還可以根據(jù)客觀情況(性能要求,源與端的資源占用等)在源和端之間加一層,應(yīng)用特殊算法和技術(shù)進(jìn)行處理。以下主要討論源上,即SQL上的分頁。
分頁的問題其實就是在滿足條件的一堆有序數(shù)據(jù)中截取當(dāng)前所需要展示的那部分。實際上各種數(shù)據(jù)庫都考慮到分頁問題而內(nèi)置了一些策略,比如MySql的LIMIT,Oracle的ROWNUM和ROW_NUMBER(),SqlServer的TOP和ROW_NUMBER(),基于此我們可以得到一系列分頁的方法。
1、 基于MySql的LIMIT和Oracle的ROWNUM,可以直接限制返回區(qū)間(以MySql為例,注意使用Oracle的ROWNUM時要應(yīng)用子查詢):
方法一、直接限制返回區(qū)間
SELECT * FROM table WHERE 查詢條件 ORDER BY 排序條件 LIMIT ((頁碼-1)*頁大小),頁大小;
優(yōu)點:寫法簡單。
缺點:當(dāng)頁碼和頁大小過大時,性能明顯下降。
適用:數(shù)據(jù)量不大。
2、基于LIMIT(MySql)、ROWNUM(Oracle)和TOP(SqlServer),他們可以限制返回的行數(shù),因此可以得到以下兩套通用的方法(以SqlServer為例):
方法二、NOT IN
SELECT TOP 頁大小 * FROM table WHERE 主鍵 NOT IN ( SELECT TOP (頁碼-1)*頁大小 主鍵 FROM table WHERE 查詢條件 ORDER BY 排序條件 ) ORDER BY 排序條件
優(yōu)點:通用性強。
缺點:當(dāng)數(shù)據(jù)量較大時向后翻頁,NOT IN中的數(shù)據(jù)過大會影響性能。
適用:數(shù)據(jù)量不大。
方法三、MAX
SELECT TOP 頁大小 * FROM table WHERE 查詢條件 AND id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP ((頁碼-1)*頁大小) id FROM table WHERE 查詢條件 ORDER BY id ) AS tempTable ) ORDER BY id
優(yōu)點:速度快,特別是當(dāng)id為主鍵時。
缺點:適用面窄,要求排序條件單一且可比較。
適用:簡單排序(特殊情況也可嘗試轉(zhuǎn)換成類似可比較值處理)。
3、基于SqlServer和Oracle的ROW_NUMBER(),可以得到返回數(shù)據(jù)的行號,基于此在限制返回區(qū)間得到如下方法(以SqlServer為例):
方法四、ROW_NUMBER()
SELECT TOP 頁大小 * FROM ( SELECT TOP (頁碼*頁大小) ROW_NUMBER() OVER (ORDER BY 排序條件) AS RowNum, * FROM table WHERE 查詢條件 ) AS tempTable WHERE RowNum BETWEEN (頁碼-1)*頁大小+1 AND 頁碼*頁大小 ORDER BY RowNum
優(yōu)點:在數(shù)據(jù)量較大時相比NOT IN有優(yōu)勢。
缺點:小數(shù)據(jù)量時不如NOT IN。
適用:大部分分頁查詢需求。
到此這篇關(guān)于SQL實現(xiàn)分頁查詢方法總結(jié)的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助,也希望大家多多支持腳本之家。
相關(guān)文章
Navicat?Premium?15?linux?安裝與激活?ArchLinux?2022最新教程(完整激活
navicat?premium?mac是一款強大數(shù)據(jù)庫管理軟件,通過navicat?premium?15?用戶快速輕松地構(gòu)建,管理和維護(hù)您的數(shù)據(jù)庫,結(jié)合了其他Navicat軟件使用更有意想不到的功能,這篇文章主要介紹了Navicat?Premium?15?linux?安裝與激活?ArchLinux?2022,需要的朋友可以參考下2023-01-01一款高顏值且免費的 SQL 開發(fā)工具之Beekeeper Studio詳解
今天給大家推薦一款適用于Windows,Linux和Mac的跨平臺免費的開源SQL編輯器和數(shù)據(jù)庫管理應(yīng)用程序 —— beekeeper-studio。對Beekeeper Studio 安裝使用教程感興趣的朋友一起看看吧2021-09-098種主流NoSQL數(shù)據(jù)庫系統(tǒng)特性對比和最佳應(yīng)用場景
這篇文章主要介紹了8種主流NoSQL數(shù)據(jù)庫系統(tǒng)特性對比和最佳應(yīng)用場景,對選擇一個NoSQL數(shù)據(jù)庫來說是一個不錯的參考文章,需要的朋友可以參考下2014-06-06使用DataGrip創(chuàng)建數(shù)據(jù)庫并讀取sql文件圖文教程
這篇文章主要給大家介紹了關(guān)于使用DataGrip創(chuàng)建數(shù)據(jù)庫并讀取sql文件的相關(guān)資料,DataGrip是一款數(shù)據(jù)庫管理客戶端工具,方便連接到數(shù)據(jù)庫服務(wù)器,執(zhí)行sql、創(chuàng)建表、創(chuàng)建索引以及導(dǎo)出數(shù)據(jù)等,需要的朋友可以參考下2023-11-11redis密碼設(shè)置、訪問權(quán)限控制等安全設(shè)置
這篇文章主要介紹了redis密碼設(shè)置、訪問權(quán)限控制等安全設(shè)置,需要的朋友可以參考下2014-05-05數(shù)據(jù)庫插入數(shù)據(jù)之select into from與insert into select區(qū)別詳解
能第一次接觸select...into...from...和insert into...select...有很多人都會誤解, 從表面上看都是把相關(guān)信息查詢出來,然后添加到一個表里,其實還遠(yuǎn)遠(yuǎn)沒有這么簡單,接下來,小豬就用最普通的表述給大家介紹一下這兩者間的區(qū)別2014-01-01