資料庫分頁技術大全(超級經典)

1.mysql

--記住mysql中limit後是從零開始的

主體格式如下：

select   *   from   tablename   limit   m,n

--比如要查第6行到第20行

select   *   from   tablename   limit   5,15

右邊兩種寫法等價： select   *   from   table  limit  10

select   *   from   table   limit  0,  10 --都是查詢前十行

limit [m,] n

m:為起始行（即從結果集的第幾行開始查詢），從0開始，且可以省略，即直接寫 limit n這是預設從第0行開始

n:為查詢條數，不能為負數

例子如下：select * fromtablename limit 2,20

表示從第2行開始，取20條資料

SELECT * FROM table   LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用於強制SELECT 語句返回指定的記錄數。LIMIT 接受一個或兩個數字引數。引數必須是一個整數常量。如果給定兩個引數，第一個引數指定

第一個返回記錄行的偏移量，第二個引數指定返回記錄行的最大數目。初始記錄行的偏移量是 0(而不是 1)： 為了與 PostgreSQL 相容，MySQL也支援句法：

LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; // 檢索記錄行 6-15

//為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個引數為 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 檢索記錄行 96-last.

//如果只給定一個引數，它表示返回最大的記錄行數目：

mysql> SELECT * FROM table LIMIT 5;     //檢索前 5 個記錄行

//換句話說，LIMITn 等價於 LIMIT 0,n。

2.sql server

--假設id是相應表的主鍵欄位

--排序後選出第3行到第5行

select * from ( selecttop 3 * from ( select  top 5 * from wen3order by id asc ) a order by id desc ) b order by id asc ;

--一下也是對的，而且更加直觀簡潔，但是在sqlserver2000中不支援，但是在sqlserver2005以上版本支援，順便說一下，oracle中已經沒有except,取而代之的是minus

select top 5 * fromwen3 order by id

except

select top 2 * fromwen3 order by id ;

--下面我換另一種方法 因為sql server2000中沒有支援except ,可以用notexists，還有not in,<>any等來替代。“except”在關係代數裡面就表示“集合的差”。

 

 

 

select top 5 a.* from ( select  top 5 * from wen3 order by id asc ) a whereid not in( select top 2 id  from wen3order by id ) order by id;

3.oracle

--(方法一)這種效能是最好的

select * from (selecta.*, rownum rn from (select * from table1 t order by id ) a where rownum <=8) where rn > 4;

--或者是

select * from (selecta.*, rownum rn from (select * from table1 t order by id ) a where rownum <=8) b where b.rn > 4;

--（方法二）

select a.*,rownum from(select * from table1 t order by id ) a   --記得先排序啊

where rownum <=8

minus

select a.*,rownum from(select * from table1 t order by id ) a

where  rownum <=4 ;

--上面這種方法也很好，而且一般選手想不到哦！

--(方法三)這種效能是最差的

select * from (selecta.*, rownum rn from (select * from table1 t order by id ) a )b where b.rnbetween 5 and 8;  --當然你這裡用and可以的

--這個陷阱很多啊，小心分辨哦！

--在使用ROWNUM時，只有當Order By 的欄位是主鍵時，查詢結果才會先

--排序再計算ROWNUM

4.DB2

--取前15條記錄。

select * from 表名 order by XXX [desc] FETCH FIRST 15 ROWS ONLY

--db2中取第m---n行

select * from (selectaid,aname,row_number() over(支援orderby 子句) as id from 表名) as twhere t.id between m and n

 

 

 

 

 

 

 

 

下面給出Oracle/Sqlserver/MySQL三種資料庫先出第m至第n條記錄的方法：

獲取按照F1欄位升序排序的，從m至n行記錄

注意，F1一定要做索引或主鍵，最好是主鍵

SqlServer

select t2.* from (Select top n-m t1.* from (Select top n * from Tablename as torder by t.F1) as t1 order by t1.F1 desc) as t2 order by t2.F1

select t2.* from
(
select top 20 t1.* from
(
select top 500020 * from dbgen as t order by t.F1
)
as t1 order by t1.F1 desc
)
as t2 order by t2.F1

Oracle

select * from (select rownum r, t.* from Tablename t where r<n) t1 wheret1.r > m and t1.r <= m

select * from (select rownum r, t.* from DBTEST t where rownum<300020) sswhere ss.r > 300000 and ss.r <= 300020

MySQL

最簡單
select * from Tablename t order by t.F1 limit n,m-n+1
select * from DBtest t order by t.F1 limit 300000,20

效能對比：
三者差不多少！
上面例項中的語句在50萬條記錄的表中選取第50萬左右的記錄20條，均不超過1秒。

 

 

 

 

 

 

Oracle、DB2、SQLSERVER、Mysql、Access分頁SQL語句梳理

Oracle、DB2、SQLSERVER、Mysql、Access分頁SQL語句梳理
最近把平時在專案中常用到的資料庫分頁sql總結了下。大家可以貼出分頁更高效的sql語句。
sqlserver分頁
第一種分頁方法
需用到的引數:
pageSize 每頁顯示多少條資料
pageNumber 頁數 從客戶端傳來
totalRecouds 表中的總記錄數 select count (*) from 表名
totalPages 總頁數
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
pages 計算前pages 條資料
pages= pageSize*(pageNumber-1)
SQL語句:
select top pageSize * from 表名 where id not in (selecttop pages id from 表名 order by id) order by id
第二種分頁方法
pageSize 每頁顯示多少條資料
pageNumber 頁數 從客戶端傳來
pages=pageSize*(pageNumber-1)+1
select top pageSize * from 表名 where id>=(selectmax(id) from (select top pages id from 表名 order by idasc ) t )

mysql分頁
需用到的引數:
pageSize 每頁顯示多少條資料
pageNumber 頁數 從客戶端傳來
totalRecouds 表中的總記錄數 select count (*) from 表名
totalPages 總頁數
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
pages 起始位置
pages= pageSize*(pageNumber-1)
SQL語句:
select * from 表名 limit pages, pageSize;
mysql 分頁依賴於關鍵字 limit 它需兩個引數:起始位置和pageSize
起始位置=頁大小*(頁數-1)
起始位置=pageSize*(pageNumber -1)

oracle分頁
pageSize 每頁顯示多少條資料
pageNumber 頁數 從客戶端傳來
totalRecouds 表中的總記錄數 select count (*) from 表名
totalPages 總頁數
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
startPage 起始位置
startPage= pageSize*(pageNumber-1)+1
endPage=startPage+pageSize
SQL語句
select a.* from
(
   select rownum num ,t.* from 表名 t where 某列=某值 order by id asc
)a
where a.num>=startPage and a.num<endPage

db2分頁
int startPage=1 //起始頁
int endPage;     //終止頁
int pageSize=5; //頁大小
int pageNumber=1 //請求頁

startPage=(pageNumber-1)*pageSize+1
endPage=(startPage+pageSize);

SQL語句
select * from (select 欄位1,欄位2,欄位3,欄位4,欄位5,rownumber()over(order by 排序欄位 asc ) as rowid from 表名 )as a where a.rowid >= startPage AND a.rowid <endPage

access分頁
pageSize 每頁顯示多少條資料
pageNumber 頁數 從客戶端傳來
pages=pageSize*(pageNumber-1)+1
SQL語句
select top pageSize * from 表名 where id>=(selectmax(id) from (select top pages id from 表名 order by idasc ) t )