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中的分页代码:
sql代码:
--讲查询出来的结果集作为一张临时表
with tempTable as (
select Row_Number() over (order by id desc) as RowNum, * from tableName
)
--从临时表中取得指定行号区间的行
SELECT * FROM tempTable where RowNum between @startIndex and @endIndex
注:ROW_NUMBER() OVER (ORDER BY id desc)为SQL2005新增函数,表示取出每一列的行号
三、分页存储过程SQL代码:
1、第一种UP_GetRecordByPage,包含计算总条数(推荐)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途:分页存储过程
--说明:
------------------------------------
CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 排序字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '1=1' -- 查询条件 (注意: 不要加 where)
AS
declare @begin int ,@end int,@sql varchar(4000),@order varchar(5)
set @begin=@PageSize* (@PageIndex-1) +1
set @end=@begin+@PageSize-1
set @order=''
if(@OrderType=1)
set @order=' desc'
set @sql='
select * from
(
select
ROW_NUMBER() over(order by ' + @fldName + @order +' ) as od,
*
from '+@tblName +'
where ' + @strWhere +
') as tbl
where od between ' + rtrim(ltrim(str(@begin)))+' and ' + rtrim(ltrim(str(@end)))
exec (@sql)
if(@IsReCount=1)
exec('select count(*) from '+ @tblName+' where ' +@strWhere)
用法:
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetList(int pageSize,int pageIndex,string strWhere,bool isCount)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageIndex", SqlDbType.Int),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "Whir_MemberDownRuleValue";
parameters[1].Value = "MemberLever";
parameters[2].Value = pageSize;
parameters[3].Value = pageIndex;
parameters[4].Value = isCount == true ? 1 : 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere;
return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 牛腩
-- Create date: 2009-07-22 12:41
-- Description: 分页,用到了ROW_NUMBER()
-- =============================================
ALTER PROCEDURE [dbo].[proc_ShowPage]
@tblName varchar(255), -- 表名,也可传入inner join内连接
@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)
一、比较万能的分页:
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中的分页代码:
sql代码:
--讲查询出来的结果集作为一张临时表
with tempTable as (
select Row_Number() over (order by id desc) as RowNum, * from tableName
)
--从临时表中取得指定行号区间的行
SELECT * FROM tempTable where RowNum between @startIndex and @endIndex
注:ROW_NUMBER() OVER (ORDER BY id desc)为SQL2005新增函数,表示取出每一列的行号
三、分页存储过程SQL代码:
1、第一种UP_GetRecordByPage,包含计算总条数(推荐)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途:分页存储过程
--说明:
------------------------------------
CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 排序字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '1=1' -- 查询条件 (注意: 不要加 where)
AS
declare @begin int ,@end int,@sql varchar(4000),@order varchar(5)
set @begin=@PageSize* (@PageIndex-1) +1
set @end=@begin+@PageSize-1
set @order=''
if(@OrderType=1)
set @order=' desc'
set @sql='
select * from
(
select
ROW_NUMBER() over(order by ' + @fldName + @order +' ) as od,
*
from '+@tblName +'
where ' + @strWhere +
') as tbl
where od between ' + rtrim(ltrim(str(@begin)))+' and ' + rtrim(ltrim(str(@end)))
exec (@sql)
if(@IsReCount=1)
exec('select count(*) from '+ @tblName+' where ' +@strWhere)
用法:
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetList(int pageSize,int pageIndex,string strWhere,bool isCount)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageIndex", SqlDbType.Int),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "Whir_MemberDownRuleValue";
parameters[1].Value = "MemberLever";
parameters[2].Value = pageSize;
parameters[3].Value = pageIndex;
parameters[4].Value = isCount == true ? 1 : 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere;
return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
}
前台aspx调用:
DataSet ds = proBLL.GetList(AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, getProductType(type), true);
总记录: int total = ds.Tables[1].Rows[0][0];
结果集:ds.Tables[0].DefaultView
===================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 牛腩
-- Create date: 2009-07-22 12:41
-- Description: 分页,用到了ROW_NUMBER()
-- =============================================
ALTER PROCEDURE [dbo].[proc_ShowPage]
@tblName varchar(255), -- 表名,也可传入inner join内连接
@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语句
http://li150dan.blog.163.com/blog/static/1722922972012320101847148/