SQL分页语句三方案

方法一：

SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN
          (
          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
          )
ORDER BY id

 方法二：

SELECT TOP 页大小 *
FROM table1
WHERE id >
          (
          SELECT ISNULL(MAX(id),0)
          FROM
                (
                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
                ) A
          )
ORDER BY id

 方法二倒序：

SELECT TOP 页大小 *
FROM table1
WHERE ID <=
    (
    SELECT ISNULL(MIN(ID),(SELECT MAX(ID) FROM table1 ))
    FROM
        (
        SELECT TOP (页大小*(页数-1)) ID FROM tbl_files ORDER BY ID DESC
        ) A
    )
ORDER BY ID DESC

方法三：

SELECT TOP 页大小 *
FROM
        (
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
        ) A
WHERE RowNumber > 页大小*(页数-1)

分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用

例子：

以下轉載至http://www.cnblogs.com/zcttxs/archive/2012/04/01/2429151.html

2.几种典型的分页sql，下面例子是每页50条，198*50=9900，取第199页数据。

1:row_number()  over()
select  top 50  * from (
select Row_Number() over(order by id) as rownumber ,* from table)a
where  rownumber >9900

select  * from (
select row_number() over(order by id)as rownumber,*from table)a
where rownumber >9000  adn rownuber<9951

2 not in 

select top 50 * from  table
where id not in (select top 9900  id  from table  order by id)
order by id

  

   ALTER PROCEDURE   存儲過程名
  (
 
    @tbName VARCHAR(255),            --表名
    @tbGetFields VARCHAR(1000)= '*',--返回字段
    @OrderfldName VARCHAR(255),        --排序的字段名
    @PageSize INT=20,               --页尺寸
    @PageIndex INT=1,               --页码
    @OrderType bit = 0,                --0升序，非0降序
    @strWhere VARCHAR(1000)='',     --查询条件
     --@TotalCount INT OUTPUT            --返回总记

  )
AS

   BEGIN
    DECLARE @strSql VARCHAR(5000)    --主语句
    DECLARE @strSqlCount NVARCHAR(500)--查询记录总数主语句
    DECLARE @strOrder VARCHAR(300) -- 排序类型
 IF ISNULL(@strWhere,'')<>''
   set @strSqlCount='select @TotalCout=count(*) from '+@tbName+'
  where 1=1'+@strWhere
  else set @strSqlCount='select @TotalCount=count(*) from '+@tbName


      ------分頁
   IF  @PageIndex <=0 set @PageIndex =1
   IF(@OrderType<>0)  set @strOrder='ORDER BY'+@OrderfldName+'DESC'
   ELSE SET @strOrder=' ORDER by'+@OrderfldName+' ASC'
 
   SET @strSql='SELECT * FROM
    (SELECT ROW_NUMBER() OVER('+@strOrder+') RowNo,'+@tbGetFields+'FROM'+@tbName +' WHERE 1=1'+@strWhere+')tb
   where tb.RowNo between '+str((@PageIndex-1)*PageSize+1)+'AND' 
+str(@PageIndex * @PageSize)
  
   exec(@strSql)
  select  @TotalCount
end