最近又遇到分页的问题,首先就是页面程序中,然后又是SQL。网上找来找去,简单的分页有,复杂的也有;性能比较好的有,比较差的也有。呼,现在整理整理,备各种环境使用。
方法1:
- SELECT TOP 页大小 *
- FROM table1
- WHERE id NOT IN
- (
- SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
- )
- ORDER BY id
适用于SQL SERVER 2000和2005
方法2:
- SELECT TOP 页大小 *
- FROM
- (
- SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNum,* FROM table1
- ) A
- WHERE RowNum > 页大小*(页数-1)
适用于SQL SERVER 2005
方法3:
- with temptbl as
- (
- SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS RowNum, *
- from table t where 条件1 and 条件2
- )
- SELECT * FROM temptbl
- where RowNum between (页数-1)*页大小+1 and 页数*页大小
适用于SQL SERVER 2005
相关存储过程:
方法1:
- create procedure 存储过程名
- (@变量 Int,
- @pagesize int,
- @pageindex int,
- @docount bit)
- as
- set nocount on
- if(@docount=1)
- select count(*) from 表名 where 子句
- else
- begin
- declare @indextable table(id int identity(1,1),nid int)
- declare @PageLowerBound int
- declare @PageUpperBound int
- set @PageLowerBound=(@pageindex-1)*@pagesize
- set @PageUpperBound=@PageLowerBound+@pagesize
- set rowcount @PageUpperBound
- insert into @indextable(nid) select 标识字段名 from 表名 where 子句 order by 排序字段名 desc
- select O.*(字段列表) from 表名 O,@indextable t where O.标识字段名=t.nid
- and t.id between @PageLowerBound+1 and @PageUpperBound order by t.id
- end
- set nocount off
方法2:
- ALTER PROCEDURE [dbo].[Pg_Paging]
- @Tables varchar(1000), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID
- @PK varchar(100), --主键,可以带表头 a.AID
- @Sort varchar(200) = '', --排序字段
- @PageNumber int = 1, --开始页码
- @PageSize int = 10, --页大小
- @Fields varchar(1000) = '*',--读取字段
- @Filter varchar(1000) = NULL,--Where条件
- @Group varchar(1000) = NULL, --分组
- @isCount bit = 0 --1 --是否获得总记录数
- AS
- --
- --select * from GL_NEWS order by GN_UPDATE_DATE DESC
- --exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 0
- DECLARE @strFilter varchar(2000)
- declare @sql varchar(8000)
- IF @Filter IS NOT NULL AND @Filter != ''
- BEGIN
- SET @strFilter = ' WHERE ' + @Filter + ' '
- END
- ELSE
- BEGIN
- SET @strFilter = ''
- END
- if @isCount = 1 --只获得记录条数
- begin
- set @sql = 'SELECT Count(*) FROM ' + @Tables + @strFilter
- end
- else
- begin
- if @Sort = ''
- set @Sort = @PK + ' DESC '
- IF @PageNumber < 1
- SET @PageNumber = 1
- if @PageNumber = 1 --第一页提高性能
- begin
- set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ ' from ' + @Tables + ' ' + @strFilter + ' ORDER BY '+ @Sort
- end
- else
- begin
- /**//**//**//*Execute dynamic query*/
- DECLARE @START_ID varchar(50)
- DECLARE @END_ID varchar(50)
- SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
- SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
- set @sql = ' SELECT '+@Fields+ '
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum,
- '+@Fields+ '
- FROM '+@Tables+') AS D
- WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
- END
- END
- --print @sql
- EXEC(@sql)
方法3:
- SET QUOTED_IDENTIFIER ON
- GO
- SET ANSI_NULLS ON
- GO
- CREATE PROCEDURE Paging_Asc_Desc
- @Tables varchar(1000),
- @PK varchar(100),
- @Sort varchar(200) = NULL,
- @PageNumber int = 1,
- @PageSize int = 10,
- @Fields varchar(1000) = '*',
- @Filter varchar(1000) = NULL,
- @Group varchar(1000) = NULL,
- @isCount bit = 0 --1时返回记录条数
- AS
- /**//*Find the @PK type*/
- DECLARE @PKTable varchar(100)
- DECLARE @PKName varchar(100)
- DECLARE @type varchar(100)
- DECLARE @prec int
- IF CHARINDEX('.', @PK) > 0
- BEGIN
- SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
- SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
- END
- ELSE
- BEGIN
- SET @PKTable = @Tables
- SET @PKName = @PK
- END
- SELECT @type=t.name, @prec=c.prec
- FROM sysobjects o
- JOIN syscolumns c on o.id=c.id
- JOIN systypes t on c.xusertype=t.xusertype
- WHERE o.name = @PKTable AND c.name = @PKName
- IF CHARINDEX('char', @type) > 0
- SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
- DECLARE @strPageSize varchar(50)
- DECLARE @strRows varchar(50)
- DECLARE @strFilter varchar(8000)
- DECLARE @strGroup varchar(8000)
- DECLARE @strSortColumn varchar(4000)
- DECLARE @strSortDesc varchar(4000)
- /**//*Default Sorting*/
- IF @Sort IS NULL
- SET @Sort = @PK
- /**//*Set sorting variables.*/
- IF CHARINDEX('DESC',@Sort)>0
- BEGIN
- SET @strSortDesc = REPLACE(@Sort, 'DESC', 'ASC')
- SET @strSortColumn = ', ' + REPLACE(@Sort, 'DESC', '')
- END
- ELSE
- BEGIN
- IF CHARINDEX('ASC', @Sort) = 0
- BEGIN
- SET @strSortDesc = @Sort + ' DESC'
- SET @strSortColumn = ', ' + @Sort
- END
- ELSE
- BEGIN
- SET @strSortDesc = REPLACE(@Sort, 'ASC', 'DESC')
- SET @strSortColumn = ', ' + REPLACE(@Sort, 'ASC', '')
- END
- END
- IF @Sort = @PK
- SET @strSortColumn = ''
- /**//*Default Page Number*/
- IF @PageNumber < 1
- SET @PageNumber = 1
- /**//*Set paging variables.*/
- SET @strPageSize = CONVERT(varchar(50), @PageSize)
- SET @strRows = CONVERT(varchar(50), (@PageSize * (@PageNumber - 1) + @PageSize))
- /**//*Set filter & group variables.*/
- IF @Filter IS NOT NULL AND @Filter != ''
- BEGIN
- SET @strFilter = ' WHERE ' + @Filter + ' '
- END
- ELSE
- BEGIN
- SET @strFilter = ''
- END
- IF @Group IS NOT NULL AND @Group != ''
- SET @strGroup = ' GROUP BY ' + @Group + ' '
- ELSE
- SET @strGroup = ''
- if @isCount = 1
- begin
- EXEC('SELECT Count(*) FROM ' + @Tables + @strFilter + ' ' + @strGroup )
- end
- else
- begin
- /**//*Execute dynamic query*/
- EXEC(
- 'DECLARE @tblPK TABLE (
- PK ' + @type + ' NOT NULL PRIMARY KEY
- )
- INSERT INTO @tblPK SELECT TOP ' + @strPageSize + ' ' + @PK + ' FROM (SELECT TOP ' + @strRows + ' ' + @PK + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ') AS ' + @PKTable + ' ORDER BY ' + @strSortDesc + '
- SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
- )
- end
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- SET ANSI_NULLS ON
- GO
方法4:分2000和2005
- Create PROCEDURE [dbo].[Basic_Pagination2000]
- @tblName varchar(255), -- 表名
- @fidlelist varchar(2000), --要查询字段
- @fldName varchar(255), -- 排序字段
- @PageSize int, -- 页尺寸
- @PageIndex int, -- 页码
- @IsReCount bit, -- 返回记录总数, 非 0 值则返回
- @OrderType bit, -- 设置排序类型, 非 0 值则降序
- @strWhere varchar(1000) -- 查询条件 (注意: 不要加 where)
- AS
- declare @strSQL varchar(6000) -- 主语句
- declare @strTmp varchar(100),@tmpwhere varchar(200) -- 临时变量
- declare @strOrder varchar(400) -- 排序类型
- if @OrderType != 0
- begin
- set @strTmp = '<(select min'
- set @strOrder = ' order by [' + @fldName +'] desc'
- end
- else
- begin
- set @strTmp = '>(select max'
- set @strOrder = ' order by [' + @fldName +'] asc'
- end
- set @tmpwhere='';
- if(@strWhere!='')
- begin
- set @tmpwhere=' where '+@strWhere;
- end
- if @PageIndex = 1
- begin
- set @strSQL = 'select top ' + str(@PageSize) +' '+@fidlelist+' '+'from ['
- + @tblName + '] ' + @tmpwhere + ' ' + @strOrder
- end
- else
- begin
- set @strSQL = 'select top ' + str(@PageSize) + ' '+@fidlelist+' '+'from ['
- + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
- + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
- + @fldName + '] from [' + @tblName + '] ' + @tmpwhere + ' '
- + @strOrder + ') as tblTmp) ' + @tmpwhere + ' ' + @strOrder
- end
- exec(@strSQL)
- if @IsReCount != 0
- begin
- set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+ @strWhere
- exec (@strSQL)
- end
- ALTER PROCEDURE [dbo].[Basic_Pagination2005]
- @tblName nvarchar(200), --表名
- @fidlelist nvarchar(1000), --要查询字段
- @fldName nvarchar(100), --排序字段
- @PageSize int, --页尺寸
- @PageIndex int, --页码
- @IsReCount bit , -- 返回记录总数, 非 0 值则返回
- @OrderType bit, -- 设置排序类型, 非 0 值则降序
- @strWhere nvarchar(1000) --查询条件
- AS
- declare @sqlstr nvarchar(4000),@tmpwhere nvarchar(4000),@tmporder nvarchar(100)
- BEGIN
- if @OrderType != 0
- begin
- set @tmporder = @fldName +' desc '
- end
- else
- begin
- set @tmporder = @fldName +' asc '
- end
- set @tmpwhere='';
- if(@strWhere!='')
- begin
- set @tmpwhere=' where '+@strWhere;
- end
- set @sqlstr=N'select * from(select '+@fidlelist+', ROW_NUMBER() OVER(order by '+@tmporder+') as row from '+@tblName+@tmpwhere+') tmp where row between '+cast(((@PageIndex-1)*@PageSize+1) as nvarchar)+' and '+cast(@PageIndex*@PageSize as nvarchar);
- exec sp_executesql @sqlstr
- if @IsReCount != 0
- begin
- set @sqlstr=N'select count(*) as Total from '+ @tblName+@tmpwhere
- exec sp_executesql @sqlstr
- end
- END
方法5:这个比较有创意
- 使用系统表根据存储过程名字生成ADO.NET数据库访问代码
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROCEDURE [dbo].[sqltoolforexcuteandadapter]
- (
- @objName nvarchar(100),--存储过程名称
- @isexcute int --是否为execute 或者是sqladapter 0是execute,1是sqladapter
- )
- AS
- SET NOCOUNT ON
- DECLARE @parameterCount int
- DECLARE @errMsg varchar(100)
- DECLARE @parameterAt varchar(1)
- DECLARE @connName varchar(100)
- DECLARE @outputValues varchar(100)
- --Change the following variable to the name of your connection instance
- SET @connName='conn.Connection'
- SET @parameterAt=''
- SET @outputValues=''
- SELECT
- dbo.sysobjects.name AS ObjName,
- dbo.sysobjects.xtype AS ObjType,
- dbo.syscolumns.name AS ColName,
- dbo.syscolumns.colorder AS ColOrder,
- dbo.syscolumns.length AS ColLen,
- dbo.syscolumns.colstat AS ColKey,
- dbo.syscolumns.isoutparam AS ColIsOut,
- dbo.systypes.xtype
- INTO #t_obj
- FROM
- dbo.syscolumns INNER JOIN
- dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
- dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
- WHERE
- (dbo.sysobjects.name = @objName)
- AND
- (dbo.systypes.status <> 1)
- ORDER BY
- dbo.sysobjects.name,
- dbo.syscolumns.colorder
- SET @parameterCount=(SELECT count(*) FROM #t_obj)
- IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName
- IF(@errMsg is null)
- BEGIN
- print 'SqlConnection conn = new SqlConnection("");
- SqlCommand com = new SqlCommand("'+@objName+'", conn);'
- print 'com.CommandType = CommandType.StoredProcedure;'
- PRINT ' SqlParameter[] Parameters = new SqlParameter[' +
- cast(@parameterCount as varchar) + '];'
- PRINT ''
- DECLARE @source_name nvarchar,
- @source_type varchar,
- @col_name nvarchar(100),
- @col_order int,
- @col_type varchar(20),
- @col_len int,
- @col_key int,
- @col_xtype int,
- @col_redef varchar(20),
- @col_isout tinyint
- DECLARE cur CURSOR FOR
- SELECT * FROM #t_obj
- OPEN cur
- -- Perform the first fetch.
- FETCH NEXT FROM cur INTO
- @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
- if(@source_type=N'U') SET @parameterAt='@'
- -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET @col_redef=(SELECT CASE @col_xtype
- WHEN 34 THEN 'Image'
- WHEN 35 THEN 'Text'
- WHEN 36 THEN 'UniqueIdentifier'
- WHEN 48 THEN 'TinyInt'
- WHEN 52 THEN 'SmallInt'
- WHEN 56 THEN 'Int'
- WHEN 58 THEN 'SmallDateTime'
- WHEN 59 THEN 'Real'
- WHEN 60 THEN 'Money'
- WHEN 61 THEN 'DateTime'
- WHEN 62 THEN 'Float'
- WHEN 99 THEN 'NText'
- WHEN 104 THEN 'Bit'
- WHEN 106 THEN 'Decimal'
- WHEN 122 THEN 'SmallMoney'
- WHEN 127 THEN 'BigInt'
- WHEN 165 THEN 'VarBinary'
- WHEN 167 THEN 'VarChar'
- WHEN 173 THEN 'Binary'
- WHEN 175 THEN 'Char'
- WHEN 231 THEN 'NVarChar'
- WHEN 239 THEN 'NChar'
- ELSE '!MISSING'
- END AS C)
- --Write out the parameter
- PRINT ' Parameters[' + cast(@col_order-1 as varchar)
- + '] = new SqlParameter("' + @parameterAt + @col_name
- + '", SqlDbType.' + @col_redef
- + ');'
- --Write out the parameter direction it is output
- IF(@col_isout=1)
- BEGIN
- PRINT ' Parameters['+ cast(@col_order-1 as varchar)
- +'].Direction=ParameterDirection.Output;'
- SET @outputValues=@outputValues+' ?=Parameters['+
- cast(@col_order-1 as varchar) +'].Value;'
- END
- ELSE
- BEGIN
- --Write out the parameter value line
- PRINT ' Parameters['+ cast(@col_order-1 as varchar) + '].Value = ?;'
- END
- --If the type is a string then output the size declaration
- IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35)
- BEGIN
- PRINT ' Parameters[' + cast(@col_order-1 as varchar) +
- '].Size=' + cast(@col_len as varchar) + ';'
- END
- -- This is executed as long as the previous fetch succeeds.
- FETCH NEXT FROM cur INTO
- @source_name,@source_type,@col_name,@col_order,
- @col_len,@col_key,@col_isout,@col_xtype
- END
- PRINT ''
- print ' com.Parameters.AddRange(Parameters);'
- if @isexcute = 0 --使用的execute方法执行sql语句
- begin
- print 'try
- {
- conn.Open();
- com.ExecuteNonQuery();
- }
- catch (Exception ee)
- {
- throw ee;
- }
- finally
- {
- conn.Close();
- }'
- end
- else if @isexcute = 1--需要返回数据集的话使用这个
- begin
- print 'try
- {
- da.Fill(ds);
- }
- catch (Exception ee)
- {
- throw ee;
- }
- finally
- {
- //do what you want to do or dispose resoures.
- }'
- end
- CLOSE cur
- DEALLOCATE cur
- END
- if(LEN(@errMsg)>0) PRINT @errMsg
- DROP TABLE #t_obj
- SET NOCOUNT ON
- 测试代码: sqltoolforexcuteandadapter 'YourProcName',1--or 0
- 显示出来的结果是:
- SqlConnection conn = new SqlConnection("");
- SqlCommand com = new SqlCommand("YourProcName", conn);
- com.CommandType = CommandType.StoredProcedure;
- SqlParameter[] Parameters = new SqlParameter[1];
- Parameters[0] = new SqlParameter("@yourparam", SqlDbType.VarChar);
- Parameters[0].Value = ?;
- Parameters[0].Size=6;
- com.Parameters.AddRange(Parameters);
- try
- {
- da.Fill(ds);
- }
- catch (Exception ee)
- {
- throw ee;
- }
- finally
- {
- //do what you want to do or dispose resoures.
- }
恩,方法很多,虽然有些都大同小异,争论点也很多,比如要不要拼接SQL语句。有人说损失性能比较大,但又没给出具体解决方案。还有就是有的遇到100W条记录的时候,分页只给显示到多少记录位置,恩,这个是很好提议。100W记录的话,确实没人会一条条的查看完整的。
呼。问题还多。。。还得自己整理。毕竟东西是别人的,拿来主意不太好。