SQL分页语句及简单的SQL分页存储过程

    假分页：从数据库中选择所有的记录后再进行分页

    真分页：只从数据库中选择当前页的记录

   

 

select top 每页显示的记录数 * from topic where id not in
 (select top （当前的页数-1）×每页显示的记录数 id from topic order by id desc)
 order by id desc

  

    需要注意的是在access中不能是top 0，所以如果数据只有一页的话就得做判断了。。

 

    SQL2005中的分页代码:

 

   

with temptbl as (
  SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, 
  ...
)
SELECT * FROM temptbl where Row between @startIndex and @endIndex

  

 

   

    

   分页存储过程:

create procedure proc_pager
(	@startIndex int,--开始记录数
	@endIndex int	--结束记录数
)
as
begin
declare @indextable table(id int identity(1,1),nid int)
insert into @indextable(nid) select orderid from orders order by orderid desc
select * 
from orders o
inner join @indextable i
on o.orderid=i.nid
where i.id between @startIndex and @endIndex
order by i.id
end

 

 

   

create procedure proc_pager1
(	@pageIndex int, -- 要选择第X页的数据
	@pageSize int -- 每页显示记录数
)
AS
BEGIN
	declare @sqlStr varchar(500)
	set @sqlStr='select top '+convert(varchar(10),@pageSize)+
	' * from orders where orderid not in(select top '+
	convert(varchar(20),(@pageIndex-1)*@pageSize)+
	' orderid from orders) order by orderid'
	exec (@sqlStr)
END

 

   下面这个仅适用sql2005

 

  

create procedure proc_pager2
(	@startIndex int,--开始记录数
	@endIndex int	--结束记录数
)
as
begin
WITH temptbl AS 
(SELECT ROW_NUMBER() OVER (ORDER BY orderid DESC) AS Row, *FROM orders)
 SELECT * FROM temptbl
where row between @startIndex and @endIndex
order by row
end

 

二:

SET ANSI_NULLS ON     
GO      
SET QUOTED_IDENTIFIER ON     
GO      
-- =============================================      
-- Description: 分页，用到了ROW_NUMBER()      
-- =============================================      
ALTER PROCEDURE [dbo].[proc_ShowPage]      
@tblName   varchar(255),       -- 表名      
@strGetFields varchar(1000) = '*', -- 需要返回的列，默认*      
@strOrder varchar(255)='',      -- 排序的字段名，必填      
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC      
@PageSize   int = 10,          -- 页尺寸,默认10      
@PageIndex int = 1,           -- 页码,默认1      
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)      
AS     
     
declare @strSQL   varchar(5000)      
     
if @strWhere !=''     
set @strWhere=' where '+@strWhere      
     
set @strSQL=      
'SELECT * FROM ('+      
    'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strGetFields+' '+      
    'FROM ['+@tblName+'] '+@strWhere+      
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)      
     
exec (@strSQL)      
print @strSQL  -- 测试用,可在查询的时候看到生成的SQL语句    

 

 

其他:

SQL2005的简单分页程序

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 

NTILE函数 将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。(也就是根据制定的数字进行平均分组)

 

如果分区的行数不能被 expression 整除，则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序，较大的组排在较小的组前面。例如，如果总行数是 53，存储桶数是 5，则前三个存储桶每个包含 11 行，其余两个存储桶每个包含 10 行。另一方面，如果总行数可被存储桶数整除，则行数将在存储桶之间平均分布。例如，如果总行数为 50，有五个存储桶，则每个存储桶将包含 10 行。

 

同样的，使用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+1

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 @current_page * @rows_per_page