SQLServer 分页查询
SQLServer 分页查询
方式
TOP 子句
-- 每页20条记录，第1页
select top 20 * from tceshi order by ID
-- 第2页（返回ID不是前20条记录）
select top 20 * from tceshi
where ID not in(select top 20 ID from tceshi order by ID)
order by ID
ROW_NUMBER
语法
[ROW_NUMBER ( ) OVER (order by子句)]
-- 每页20条记录，第1页
select * from
(select ROW_NUMBER() over(order by ID) as RecNo from tceshi) t
where RecNo between 1 and 20
-- 第2页（按ID排序生成序号列RecNo，查询21-40的）
select * from
(select ROW_NUMBER() over(order by ID) as RecNo from tceshi) t
where RecNo between 21 and 40
SqlServer-2005 开始支持
OFFSET FETCH
语法
[ORDER BY OFFSET 跳过的行数 ROW FETCH 返回的行数 ROW ONLY]
-- 每页20条记录，第1页
select * from tceshi
order by ID offset 0 row fetch next 20 row only
-- 第2页（从排序的结果集中跳过前 20 行并且返回接下来的 20 行。）
select * from tceshi
order by ID offset 20 row fetch next 20 row only
SqlServer-2012 开始支持
