查看: 6096|回復: 0

[SQLServer] SQL Server 分頁方法匯總

發表于 2018-4-30 09:03:10
PageSize = 30

PageNumber = 201

方法一:(最常用的分頁代碼, top / not in)

  1. select top 30 UserId from UserInfo where UserId not in (select top 6000 UserId from UserInfo order by UserId) order by UserId
復制代碼

備注: 注意前后的order by 一致

方法二:(not exists, not in 的另一種寫法而已)

  1. select top 30 * from UserLog where not exists (select 1 from (select top 6000 LogId from UserLog order by LogId) a where a.LogId = UserLog.LogId) order by LogId
復制代碼

備注:EXISTS用于檢查子查詢是否至少會返回一行數據,該子查詢實際上并不返回任何數據,而是返回值True或False。此處的 select 1 from 也可以是select 2 from,select LogId from, select * from 等等,不影響查詢。而且select 1 效率最高,不用查字典表。效率值比較:1 > anycol > *

方法三:(top / max, 局限于使用可比較列排序的時候)

  1. select top 30 * from UserLog where LogId > (select max(LogId) from (select top 6000 LogId from UserLog order by LogId) a ) order by LogId
復制代碼

備注:這里max()函數也可以用于文本列,文本列的比較會根據字母順序排列,數字 < 字母(無視大小寫) < 中文字符

方法四:(row_number() over (order by LogId))

  1. select top 30 * from ( select row_number() over (order by LogId) as rownumber,* from UserLog)a
  2. where rownumber > 6000 order by LogId
復制代碼
  1. select * from (select row_number()over(order by LogId) as rownumber,* from UserLog)a
  2. where rownumber > 6000 and rownumber < 6030 order by LogId
復制代碼
  1. select * from (select row_number()over(order by LogId) as rownumber,* from UserLog)a
  2. where rownumber between 6000 and 6030 order by LogId
復制代碼

復制代碼
  1. select *
  2. from (
  3. select row_number()over(order by tempColumn)rownumber,*
  4. from (select top 6030 tempColumn=0,* from UserLog where 1=1 order by LogId)a
  5. )b
  6. where rownumber>6000
  7. row_number() 的變體,不基于已有字段產生記錄序號,先按條件篩選以及排好序,再在結果集上給一常量列用于產生記錄序號
  8. 以上幾種方法參考http://www.cnblogs.com/songjianpin/articles/3489050.html
復制代碼
復制代碼

備注: 這里rownumber方法屬于排名開窗函數(sum, min, avg等屬于聚合開窗函數,ORACLE中叫分析函數,參考文章:SQL SERVER 開窗函數簡介 )的一種,搭配over關鍵字使用。

方法五:(offset /fetch next, SQL Server 2012支持)

  1. select * from UserLog Order by LogId offset 6000 rows fetch next 30 rows only
復制代碼

備注: 性能參考文章《SQL Server 2012使用OFFSET/FETCH NEXT分頁及性能測試》

參考文檔:

1、http://blog.csdn.net/qiaqia609/article/details/41445233

2、http://www.cnblogs.com/songjianpin/articles/3489050.html

3、http://database.51cto.com/art/201108/283399.htm

轉自:http://www.cnblogs.com/shengxincai/p/6097588.html



回復

使用道具 舉報