sql 2005下 分页 更简单
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_pageWITH 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
//--楼主也可以去我的blog去看看 存储过程分页的2分算法
当然要是用分页控件 www.webdiyer.com这里的 当仁不让
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_pageWITH 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
//--楼主也可以去我的blog去看看 存储过程分页的2分算法
当然要是用分页控件 www.webdiyer.com这里的 当仁不让
写了测,测了写,写了好久,才写好……
拿出来,大家分享喽
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@keyName varchar(255), --主键
@PageSize int = 10, -- 页尺寸
@PageIndex int = 0 , -- 页码
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @beginItemIndex int --开始项
declare @endItemIndex int --结束项
set @beginItemIndex=@PageIndex*@PageSize
set @endItemIndex=@beginItemIndex+@PageSize
set @strSQl='SELECT top 10 * FROM
(
select *,
ROW_NUMBER() OVER( ORDER BY '+@keyName+' DESC ) as rowNo
from '
set @strSQL=@strSQL+@tblName+' where 1=1 '
if @strWhere!=''
begin
set @strSQL=@strSQL+@strWhere;
end
set @strSQL=@strSQL+')
AS tabeTemp
WHERE RowNo >='+cast (@beginItemIndex as varchar ) +' and RowNo <='+ cast (@endItemIndex as varchar)
print @strSQL
exec(@strSQL)
go
为什么出现ROW_NUMBER()是不可识别的函数 我在sql2005帮助忠找到了这个介绍 但是查询的时候说为不可识别的函数??