003-0002 Sql2005分页

 

从宝玉的Blog上看到了名为《Sql2005带来的分页便利》的短文，文章很短，但是却让我欣喜万分，MS终于在SQL Server上给解决了大量数据的分页问题了。以前我经常羡慕MySql用户可以使用很简单的办法来处理分页问题，而今天我们在Sql2005上也同样可以很方便的处理分页问题了。

select threadid from
(
select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads
) as T
where T.Pos > 100000 and T.Pos < 100030

以上是原文提供的查询例子，其中最重要的就是ROW_NUMBER()了，这个东西需要结合order by来使用。:)

******************************************************************************************

　　SQL2005增加了不少新特性，其中NTILE和ROW_NUMER使得我们不再为SQL如何灵活方便的分页伤脑筋了（不必再羡慕Oracle等数据库了）。
　　下面就是一个很简单的分页查询语句： DECLARE @rows_per_page AS INTEGER
DECLARE @current_page AS INTEGER
DECLARE @total_pages AS INTEGER

-- 设置每页的行数
SET @rows_per_page = 20
-- 设置要显示的页号(从1开始)
SET @current_page = 2
-- 计算总页数
SELECT @total_pages = COUNT(*) / @rows_per_page
FROM testtable;

-- 列出指定页的内容
WITH t AS
(
  SELECT NTILE(@total_pages) OVER(ORDER BY id) AS page_number, *
  FROM testtable
)
SELECT * from t
WHERE page_number = @current_page 　　程序简单到可以不用说明的程度。
　　我们可以利用上述简单的语句，变化排序条件和查询表，就可以做出一个很通用的分页查询的存储过程或查询语句了。

　　同样的，使用ROW_NUMBER也可以做到分页查询： DECLARE @rows_per_page AS INTEGER
DECLARE @current_page AS INTEGER
DECLARE @start_row_num AS INTEGER

-- 设置每页的行数
SET @rows_per_page = 20
-- 设置要显示的页号(从1开始)
SET @current_page = 2
-- 设置开始行号
SET @start_row_num = (@current_page - 1) * @rows_per_page

WITH t AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_number, *
  FROM testtable
)
SELECT * from t
WHERE row_number BETWEEN @start_row_num AND @start_row_num + @rows_per_page　　似乎更简单的样子。

　　至于哪种效率更高，这需要大家实际测试了。 ///

例子：

SELECT [Rol_Id]
      ,[Rol_Name]
      ,[Rol_Dis]
  FROM [OA].[dbo].[OaRole]

select * from
(
select *, ROW_NUMBER() OVER (order by [Rol_Id]) as rownum from [OA].[dbo].[OaRole]
) as T
where T.rownum >= 2 and T.rownum <= 3

select top 2 * from
(select top 3 * from [OA].[dbo].[OaRole] order by [Rol_Id] desc) as t
order by [Rol_Id] asc