SELECT DISTINCT num,time,result from info
where num='03'
order by time DESC
请问下怎么把上面语句改成分页查询,每页5条数据这样子
------解决思路----------------------
DECLARE @pageSize INT ,@pageIndex INT
; WITH F AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY TIME DESC) AS ID
FROM
(
SELECT DISTINCT num,time,result from info
where num='03'
) AS T
)
SELECT * FROM F WHERE id> @pageSize * @pageIndex AND ID < @pageSize * ( @pageIndex + 1 )
------解决思路----------------------
with mt as (
select id , name , xtype ,row_number() over(order by id) rn
from sysobjects
where xtype = 's'
)
select id , name, xtype , (rn -1) / 5 + 1 from mt
go
id name xtype
----------- -------------------- ----- --------------------
3 sysrscols S 1
5 sysrowsets S 1
7 sysallocunits S 1
8 sysfiles1 S 1
17 syspriorities S 1
19 sysfgfrag S 2
23 sysphfg S 2
24 sysprufiles S 2
........
(45 行受影响)
------解决思路----------------------
MSSQL2012 及以上版本：
DECLARE @pageSize INT --页大小
DECLARE @pageIndex INT --页索引(从 0 开始)
set @pageSize = 5
set @pageIndex = 0
select hdate, songid, hcount
from hit
order by hdate DESC
offset (@pageIndex*@pageSize)
rows fetch next @pageSize rows only;