[SQL筆記] 分頁

R

分頁

• 方案一：Top方式分頁(但top效率不是很高)
• 方案二：使用row_number分頁

row_number 分頁

--假設每頁5條數據
--第1頁: 1 to 5
--第2頁: 6 to 10
select * from
(select ROW_NUMBER() over(order by StuId) RowId, * from Student) Temp
where Temp.RowId between (當前頁-1)*頁碼大小+1 and (當前頁*頁碼大小)

--使用變量
declare @PageSize int = 5 --頁碼大小
declare @PageIndex int = 1 --當前頁
select * from
(select ROW_NUMBER() over(order by StuId) RowId, * from Student) Temp
where Temp.RowId between (@PageIndex-1)*@PageSize+1 and (@PageIndex*@PageSize)

詳細說明

--建立測試資料
create table Student
(
    StuId int primary key identity(1,2),--自動編號
    StuName varchar(20),
    StuSex varchar(4)
)
insert into Student(StuName,StuSex)
values('Alen','男'),
('Bili','男'),
('Celie','男'),
('Dele','男'),
('Een','男'),
('Fly','男'),
('Gun','男'),
('Hero','男')
select * from Student

--分頁
--假設每頁是5條數據
--查詢第一頁
select top 5 * from Student
--第二頁
select top 5 * from Student
where StuId not in(1,3,5,7,9)
--但未必知道編號是13579，所以裡面放子查詢
select top 5 * from Student
where StuId not in(select top 5 StuId from Student)
--第三頁，以此類推
select top 5 * from Student
where StuId not in(select top 10 StuId from Student)
--從上面，這樣我們可以得出一個規律：
select top 頁碼大小 * from Student
where StuId not in(select top 頁碼大小*(當前頁-1) StuId from Student)
--分頁方案一：Top方式分頁
--用變量表示：
--但是用 top 效率不是很高
declare @PageSize int = 5 --頁碼大小
declare @PageIndex int = 1 --當前頁
select top (@PageSize) * from Student
where StuId not in(select top (@PageSize*(@PageIndex-1)) StuId from Student)

分頁方案一：Top方式分頁

但是用 top 效率不是很高

--select top 頁碼大小 * from Student
--where StuId not in(select top 頁碼大小*(當前頁-1) StuId from Student)

declare @PageSize int = 5 --頁碼大小
declare @PageIndex int = 1 --當前頁
select top (@PageSize) * from Student
where StuId not in(select top (@PageSize*(@PageIndex-1)) StuId from Student)

分頁方案二：使用row_number 分頁

ROW_NUMBER()
row_number() over(order by 主鍵) 欄位別名 row_number() over(order by StuId) RowId

--我們可以用between-and這樣的概念去思考
select * from Student
where StuId between 1 and 5
--Step1:使用ROW_NUMBER()
select ROW_NUMBER() over(order by StuId) RowId, * from Student
--Step2:用ROW_NUMBER()查詢的結果，作為一個臨時表 Temp
select * from
(select ROW_NUMBER() over(order by StuId) RowId, * from Student) Temp
--Step3:再加上一個條件where
select * from
(select ROW_NUMBER() over(order by StuId) RowId, * from Student) Temp
where Temp.RowId between 1 and 5
--假設每頁5條數據
select * from
(select ROW_NUMBER() over(order by StuId) RowId, * from Student) Temp
where Temp.RowId between (當前頁-1)*頁碼大小+1 and (當前頁*頁碼大小)
--1: 1 to 5
--2: 6 to 10
--改成變量
declare @PageSize int = 5 --頁碼大小
declare @PageIndex int = 1 --當前頁
select * from
(select ROW_NUMBER() over(order by StuId) RowId, * from Student) Temp
where Temp.RowId between (@PageIndex-1)*@PageSize+1 and (@PageIndex*@PageSize)

MSDN 分頁與範圍結構指南
https://www.bilibili.com/video/BV1XV411C7TP?p=22