SQL分页查询语句

这个只能用再Sql2005及以上的版本

DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, 
        speaker, track, score
      FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

例子

SELECT TOP (10) * FROM hy_hotel_info
 WHERE hi_id >(SELECT MAX (hi_id) 
from (select top （2*10） hi_id 
from hy_hotel_info order by hi_id) as talTMP ) order by hi_id

以上结果是 查询每页十个  查询的是第三页


SELECT TOP (10)     --查询每页显示的数量  
 * FROM hy_hotel_info
 WHERE hi_id >(SELECT MAX (hi_id) 
from (select top （2*10） --查询的页数*每页显示的数量
 hi_id 
from hy_hotel_info order by hi_id) as talTMP ) order by hi_id

 

SELECT TOP (10) HY_Counter.cc_id, * FROM hy_hotel_info
inner join HY_Counter on CC_ID = HI_CounterId
 WHERE hi_id >(SELECT MAX (hi_id) 
from (select top 20 hi_id 
from hy_hotel_info order by hi_id) as talTMP ) 
order by HY_Counter.cc_id asc, hi_id desc

上面这条是按照点击率点击排序的sql 查询分页 是一个联合查询的分页sql 语句