SQL Server 2012分页查询新语法

SQL Server 2012分页查询新语法

假设我们有一组数据，包含五列（编号，币种,交易日期,收入金额,支出金额）

Select * From
(Values
('CNY','2022-01-01',100,50),
('CNY','2022-01-02',200,50),
('CNY','2022-01-03',300,100),
('CNY','2022-01-04',400,50),
('CNY','2022-01-05',500,60),
('CNY','2022-01-06',1000,500),
('CNY','2022-01-07',600,300),
('CNY','2022-01-08',800,400),
('CNY','2022-01-09',100,50),
('CNY','2022-01-10',0,500),
('USD','2022-01-01',100,50),
('USD','2022-01-02',200,50),
('USD','2022-01-03',300,100),
('USD','2022-01-04',400,500),
('USD','2022-01-05',500,60),
('USD','2022-01-06',400,500),
('USD','2022-01-07',600,300),
('USD','2022-01-08',800,400),
('USD','2022-01-09',100,50),
('USD','2022-01-10',10,500)
) 
TempTable(Currency,TradeDate,InValue,OutValue)

假设我们每页能显示五行数据，我们要查询第三页数据的语法如下

Declare @Page As int
Declare @PageSize As int

Set @Page=3
Set @PageSize=5

Select * From
(
	Select * From
	(Values
	(1,'CNY','2022-01-01',100,50),
	(2,'CNY','2022-01-02',200,50),
	(3,'CNY','2022-01-03',300,100),
	(4,'CNY','2022-01-04',400,50),
	(5,'CNY','2022-01-05',500,60),
	(6,'CNY','2022-01-06',1000,500),
	(7,'CNY','2022-01-07',600,300),
	(8,'CNY','2022-01-08',800,400),
	(9,'CNY','2022-01-09',100,50),
	(10,'CNY','2022-01-10',0,500),
	(11,'USD','2022-01-01',100,50),
	(12,'USD','2022-01-02',200,50),
	(13,'USD','2022-01-03',300,100),
	(14,'USD','2022-01-04',400,500),
	(15,'USD','2022-01-05',500,60),
	(16,'USD','2022-01-06',400,500),
	(17,'USD','2022-01-07',600,300),
	(18,'USD','2022-01-08',800,400),
	(19,'USD','2022-01-09',100,50),
	(20,'USD','2022-01-10',10,500)
	) 
	TempTable(ID,Currency,TradeDate,InValue,OutValue)
) T1
Order By ID
Offset (@Page -1) * @PageSize Rows
Fetch Next @PageSize Rows Only

我们来看一下执行结果