[sql server] 分頁 – 重複欄位+主鍵

-- 測試環境

if object_id('tb') is not null drop table tb
go
create table tb
(
 id int,
 name varchar(20),
 constraint pk_tb primary key (id)
 
)
go
insert tb
select 1,'A' union all
select 2,'B' union all
select 3,'B' union all
select 4,'B' union all
select 5,'B' union all
select 6,'B' union all
select 7,'B' union all
select 8,'B' union all
select 9,'A' union all
select 10,'A' union all
select 11,'A'
go
-- 按照name欄位，以5行分頁

-- 按照name欄位，以5行分頁
-- 查詢 第一頁
select top 5 * from tb order by name,id
-- 結果
/*
id          name
----------- --------------------
1           A
9           A
10          A
11          A
2           B

(5 行受影響)
*/                                                                                                                       

-- 查詢 第二頁
select top 5 * from
(
    select top 10 * from tb order by name,id
) t order by name desc,id desc
-- 結果
/*
id          name
----------- --------------------
7           B
6           B
5           B
4           B
3           B

(5 行受影響)
*/                                                                                                                       

-- 查詢 第二頁
select top 5 * from
(
    select top 15 * from tb order by name,id
) t order by name desc,id desc
-- 結果
/*
id          name
----------- --------------------
7           B
6           B
5           B
4           B
3           B

(5 行受影響)
*/