方便高效分页存储过程(感谢各位能提出宝贵的意见)
使用最简单方便,功能强大的分页存储过程。免去繁多的参数,再不用将SQL语句拆分成若干个参数了
调用只需要运行:NewPageView exec 查询字符串,当前页,显示条数
即可,查询字符串可使用任意没有语法错误的SQL语句,包括 order by
主意需要将SQL语句中的单引号用俩个单引号或^(shift+6)代替
例如,支持任意列索引,或无索引分页
exec NewPageView'select b,c,d,e,f from temp where b=^测试^',10000,20
exec NewPageView'select b,c,d,e,f from temp where b=’’测试’’',10000,20
查询结果为俩个表,表1为当前页内容,表2为记录数据,总计页数等信息
也许细心的网友发现该存储过程的核心是OW_NUMBER() OVER(),认为效率很低,其实在实际测试中效率非常的高,若在没有Order By的情况下会以 1作为索引列速度更快,在400万条6列vachar(50)的数据表中分页查询每页显示50条数据最慢用时1秒,最快0秒,在有ORDER BY的情况下最慢需要7秒,最快0秒,关键是使用方便
感谢大家支持,欢迎大家提出宝贵的意见。
/****** Object: StoredProcedure [dbo].[NewPageView] Script Date: 12/27/2012 12:24:12 ******/
/*创建日期:2007年,更新日期2012.12.27,创建人:雨 QQ:312430633*/
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
ALTERPROCEDURE [dbo].[NewPageView]
@tsql varchar(4000),----*****SQL语句
@pageindex int, ----*****页码
@PageSize int ----*****每页显示条数
as
Declare@SqlSelect varchar(4000)
Declare @UnSqlSelect varchar(4000)
Declare @orderby varchar(4000)
Declare @AllowPagingSql varchar(4000)
---判断是否排序
if CHARINDEX('order by',@tsql)<> 0
begin
set @SqlSelect=replace(substring(@tsql,1,CHARINDEX('order by',@tsql)-1),'$','''')
set @UnSqlSelect= replace(@SqlSelect,'Select','')
set @orderby=replace(substring(@tsql,CHARINDEX('order by',@tsql),len(@tsql)),'$','''')
set @AllowPagingSql=
'select * from (SELECT ROW_NUMBER() OVER('+@orderby+') AS AllowPagingId,* FROM ( select top '+convert(varchar(10),@pageindex*@PageSize)+' '
+@UnSqlSelect
+' ) as tbs1 ) as table2 where AllowPagingId between '
+convert(varchar(10),((@pageindex-1)*@PageSize+1))+' and '
+convert(varchar(10),@pageindex*@PageSize)
exec (@AllowPagingSql)
end
else
begin
set @SqlSelect=replace(@tsql,'$','''')
set @orderby=''
set @AllowPagingSql=
'select * from (SELECT ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId ,* FROM ( select *, 1 as orderbyID from ( select top '+convert(varchar(10),@pageindex*@PageSize)+' '
+@UnSqlSelect
+' ) as tbs1 ) as Tabl1 ) as table2 where AllowPagingId between '
+convert(varchar(10),((@pageindex-1)*@PageSize+1))+' and '
+convert(varchar(10),@pageindex*@PageSize)
exec (@AllowPagingSql)
end
set @AllowPagingSql='select
case
when count(*)%'+convert(varchar(10),@PageSize)+'=0 then count(*)/'+convert(varchar(10),@PageSize)+'
when count(*)%'+convert(varchar(10),@PageSize)+'<>0 then count(*)/'+convert(varchar(10),@PageSize)+'+1
end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'
exec (@AllowPagingSql)
使用示例:
exec NewPageView SQL语句,页码,每页显示条数
1、 exec NewPageView'select b,c,d,e,f from temp',10000,20
2、 exec NewPageView'select b,c,d,e,f from temp where b=^测试^',10000,20
exec NewPageView'select b,c,d,e,f from temp where b=’’测试’’',10000,20
提示:(示例2中的^替换查询语句中的单引号’,也可以使用’’俩个单引号替换单引号。)
因为SQL语句会在存储过程中当作字符串进行处理)
3、 exec NewPageView'select b,c,d,e,f from temp order by b',10000,20
200万条带Order BY用时
400万条带Order BY用时
在这里在为大家增上一段C#的分页数据生成类
///<summary>
///数ºy据Y分¤?页°3
///</summary>
///<param name="Sql">查¨¦询¡¥语®?句?</param>
///<param name="PageIndex">页°3码?</param>
///<param name="PageSize">每?页°3显?示º?条¬?数ºy</param>
///<returns></returns>
public static string PagingAll(string Sql,int PageIndex,int PageSize)
{
string selectStr = "", orderStr = "";
int rows = PageIndex * PageSize;
string val = "";
selectStr = Sql.ToUpper();
if (selectStr.IndexOf("TOP") == -1)
{
selectStr = "SELECT TOP "+ rows.ToString()+" " + selectStr.Substring(selectStr.IndexOf("SELECT"), selectStr.Length - selectStr.IndexOf("SELECT"));
}
if (selectStr.IndexOf("ORDER BY") != -1)
{
orderStr = selectStr.Substring(selectStr.IndexOf("ORDER BY"), selectStr.Length - selectStr.IndexOf("ORDER BY"));
selectStr = selectStr.Substring(0, selectStr.IndexOf("ORDER BY"));
}
if (selectStr.IndexOf("ORDER BY") == -1)
{
val = @"select * from (SELECT ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId
,* FROM ( select *, 1 as orderbyID from ( " + selectStr +@" ) as tbs1 )
as Tabl1 ) as table2 where
AllowPagingId between " + ((PageIndex - 1) * PageSize).ToString() +" and " + rows.ToString();
}
else
{
val = @"select * from (SELECT ROW_NUMBER() OVER(" + orderStr + @") AS AllowPagingId
,* FROM ( select * from ( " + selectStr +@" ) as tbs1 )
as Tabl1 ) as table2 where
AllowPagingId between " + ((PageIndex - 1) * PageSize).ToString() +" and " + rows.ToString();
}
return val;
}
C#使用示例
TextBox1.Text = PagingAll( "select * from temp", 5, 10);