sql分页汇总和比较.pdf

Sql分页方法汇总比较
作者:章立强
http://hi.baidu.com/lvjava

构建数据

1.创建表

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_PADDINGON
GO
CREATETABLE[dbo].[test_record](
[id][int]IDENTITY(1,1)NOTNULL,
[memo][varchar](max)COLLATEChinese_PRC_CI_ASNULL,
[input_time][datetime]NOTNULLCONSTRAINT
[DF_test_record_input_time]DEFAULT(getdate()),
CONSTRAINT[PK_test_record]PRIMARYKEYCLUSTERED
(
[id]ASC
)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY]
)ON[PRIMARY]

GO
SETANSI_PADDINGOFF

2.填充数据(5万条)

SETIDENTITY_INSERTtest_recordONDECLARE@iint
SET@i=1WHILE@i<=50000BEGIN
INSERT
INTOtest_record([id],memo,
input_time)
VALUES(@i,'测试'+CAST(@iASvarchar),Cast('2012-5-1'AS
datetime)+@i)
SET@i=@i+1END
SETIDENTITY_INSERTtest_recordOFF

逆序法

1.存储过程代码

setANSI_NULLSON
setQUOTED_IDENTIFIERON
go

--通用分页存储过程
--@SQL:查询语句
--@PageNO:页码，从开始,@PageSize:每页记录数,@SortType:排序方式,
@TotalSize:总记录数，返回值
ALTERPROCEDURE[dbo].[SP_SqlDataPages]
(
@SQLVARCHAR(Max),
@PageNOINT,
@PageSizeINT,
@SortTypeVARCHAR(128),
@TotalSizeINTOUTPUT
)
AS

/*声明变量*/
DECLARE@strNVARCHAR(Max)
DECLARE@SortColumnNVARCHAR(248)
DECLARE@OrginalSortNVARCHAR(24)
DECLARE@ReverseSortNVARCHAR(24)
DECLARE@temp_strNVARCHAR(Max)
DECLARE@ResultSizeint

/*设置排序变量值*/
IFCHARINDEX('desc',@SortType)>0
BEGIN
SET@SortColumn=REPLACE(@SortType,'desc','')
SET@OrginalSort='desc'
SET@ReverseSort='asc'
END
ELSE
BEGIN
SET@SortColumn=REPLACE(@SortType,'asc','')
SET@OrginalSort='asc'
SET@ReverseSort='desc'
END

/*计算查询记录总数*/
set@temp_str='select@TotalSize=count(*)from('+@SQL+')tmp'
EXECSP_EXECUTESQL@temp_str,N'@TotalSizeINTOUTPUT',@TotalSizeOUTPUT

/*计算当前应返回记录数*/
if(@TotalSize<@PageNO*@PageSize)
BEGIN
SET@ResultSize=@TotalSize%@PageSize
END
else
BEGIN
SET@ResultSize=@PageSize
END

/*组装字符串*/
SET@str='SELECT*FROM'+
'('+
'SELECTTOP'+CAST(@ResultSizeASVARCHAR(20))+'*FROM'+
'('+
'SELECTTOP'+CAST((@PageSize*(@PageNO))ASVARCHAR(20))+'*
FROM'+
'('+
'SELECTTOP'+CAST((@PageSize*(@PageNO))ASVARCHAR(20))+'*
'+
'FROM('+@SQL+')AST1'+
'ORDERBY'+@SortColumn+''+@OrginalSort+
')AST2'+
'ORDERBY'+@SortCo