|
這里面介紹一下常用的分頁方法：
1.使用top來分頁
- select top @pageSize * from table where id not in
- (select top @pageSize*(@pageIndex-1) id from table)
2.使用 ROW_NUMBER()OVER
- SELECT * FROM (SELECT ROW_NUMBER() OVER (order by T.字段名 desc )AS Row, T.* from 表名 T WHERE 條件 ) TT WHERE TT.Row between 起始位置 and 結(jié)束位置;
-
- StringBuilder strSql = new StringBuilder();
- strSql.Append("SELECT * FROM ( ");
- strSql.Append(" SELECT ROW_NUMBER() OVER (");
- if (!string.IsNullOrWhiteSpace(orderby.Trim()))
- {
- strSql.Append("order by T." + orderby);
- }
- else
- {
- strSql.Append("order by T.ID desc");
- }
- strSql.Append(")AS Row, T.* from TableName T ");
- if (!string.IsNullOrWhiteSpace(strWhere.Trim()))
- {
- strSql.Append(" WHERE " + strWhere);
- }
- strSql.Append(" ) TT");
- strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
3.使用分頁存儲過程
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_Page_v2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dbo].[sys_Page_v2]
- GO
- CREATE PROCEDURE [dbo].[sys_Page_v2]
- @PCount int output, --總頁數(shù)輸出
- @RCount int output, --總記錄數(shù)輸出
- @sys_Table nvarchar(100), --查詢表名
- @sys_Key varchar(50), --主鍵
- @sys_Fields nvarchar(500), --查詢字段
- @sys_Where nvarchar(3000), --查詢條件
- @sys_Order nvarchar(100), --排序字段
- @sys_Begin int, --開始位置
- @sys_PageIndex int, --當(dāng)前頁數(shù)
- @sys_PageSize int --頁大小
- AS
- SET NOCOUNT ON
- SET ANSI_WARNINGS ON
- IF @sys_PageSize < 0 OR @sys_PageIndex < 0
- BEGIN
- RETURN
- END
- DECLARE @new_where1 NVARCHAR(3000)
- DECLARE @new_order1 NVARCHAR(100)
- DECLARE @new_order2 NVARCHAR(100)
- DECLARE @Sql NVARCHAR(4000)
- DECLARE @SqlCount NVARCHAR(4000)
- DECLARE @Top int
- if(@sys_Begin <=0)
- set @sys_Begin=0
- else
- set @sys_Begin=@sys_Begin-1
- IF ISNULL(@sys_Where,'') = ''
- SET @new_where1 = ' '
- ELSE
- SET @new_where1 = ' WHERE ' + @sys_Where
- IF ISNULL(@sys_Order,'') <> ''
- BEGIN
- SET @new_order1 = ' ORDER BY ' + Replace(@sys_Order,'desc','')
- SET @new_order1 = Replace(@new_order1,'asc','desc')
- SET @new_order2 = ' ORDER BY ' + @sys_Order
- END
- ELSE
- BEGIN
- SET @new_order1 = ' ORDER BY ID DESC'
- SET @new_order2 = ' ORDER BY ID ASC'
- END
- SET @SqlCount = 'SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'
- + CAST(@sys_PageSize AS NVARCHAR)+') FROM ' + @sys_Table + @new_where1
- EXEC SP_EXECUTESQL @SqlCount,N'@RCount INT OUTPUT,@PCount INT OUTPUT',
- @RCount OUTPUT,@PCount OUTPUT
- IF @sys_PageIndex > CEILING((@RCount+0.0)/@sys_PageSize) --如果輸入的當(dāng)前頁數(shù)大于實(shí)際總頁數(shù),則把實(shí)際總頁數(shù)賦值給當(dāng)前頁數(shù)
- BEGIN
- SET @sys_PageIndex = CEILING((@RCount+0.0)/@sys_PageSize)
- END
- set @sql = 'select '+ @sys_fields +' from ' + @sys_Table + ' w1 '
- + ' where '+ @sys_Key +' in ('
- +'select top '+ ltrim(str(@sys_PageSize)) +' ' + @sys_Key + ' from '
- +'('
- +'select top ' + ltrim(STR(@sys_PageSize * @sys_PageIndex + @sys_Begin)) + ' ' + @sys_Key + ' FROM '
- + @sys_Table + @new_where1 + @new_order2
- +') w ' + @new_order1
- +') ' + @new_order2
- print(@sql)
- Exec(@sql)
- GO
|
