分頁sql查詢在編程的應(yīng)用很多，主要有存儲過程分頁和sql分頁兩種，我比較喜歡用sql分頁，主要是很方便。為了提高查詢效率，應(yīng)在排序字段上加索引。sql分頁查詢的原理很簡單，比如你要查100條數(shù)據(jù)中的30-40條，你先查詢出前40條，再把這30條倒序，再查出這倒序后的前十條，最后把這十條倒序就是你想要的結(jié)果。
        下面把sql分頁查詢的原理用sql語句表現(xiàn)一下：

        -- 分頁 升序（搜出的結(jié)果再倒序）
        SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 ASC) AS T ORDER BY A00 DESC

         -- 分頁 升序
        SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 ASC) AS T ORDER BY A00 DESC) AS T1 ORDER BY A00 ASC

         -- 分頁 降序（搜出的結(jié)果再倒序）
         SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 DESC) AS T ORDER BY A00 ASC

         -- 分頁 降序
         SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 DESC) AS T ORDER BY A00 ASC) AS T1 ORDER BY A00 DESC

         為了應(yīng)用的方便我把生成sql分頁查詢語句的寫成了類SplitHelp

        應(yīng)用如下：
       
       

/**//// <summary>
      /// 分頁查詢例子
      /// </summary>
      /// <param name="currentPage">當前頁</param>
      /// <param name="pagesize">每頁大小</param>
      /// <param name="count">數(shù)據(jù)總條數(shù)</param>
      /// <param name="cn">數(shù)據(jù)庫連接</param>
      /// <returns>查詢IDbCommand</returns>
      public IDbCommand Search(int currentPage, int pagesize, out int count, IDbConnection cn)
      {
         //得到IDbCommand
         IDbCommand cmd = cn.CreateCommand();
         cmd.CommandType = CommandType.Text;
        
         cmd.CommandText = "select count(tableTestID) from tableTest";
         if (cn.State != ConnectionState.Open)
            cn.Open();
         //得到數(shù)據(jù)總數(shù)
         count = (int)cmd.ExecuteScalar();

         //搜索的前n條
         int topAll = SplitPage.GetTopNum(currentPage, pagesize, count);

         //排序字段類
         AscDescClass ascDesc = new AscDescClass("tableTestID", AscDescEnum.desc);
         //ascDesc.Add("tableTestID1", AscDescEnum.desc);

         //此sql語名必須有排序,寫出要查詢前topAll條記錄的sql
         string sql = String.Format(@"select top {0} * from tableTest order by {1}", topAll, ascDesc.GetAscDesString());
 
         //最終sql
         cmd.CommandText = SplitPage.GetFinalSql(sql, ascDesc, pagesize, count, topAll, currentPage);

         return cmd;
      }
當然，要想真正提高查詢效率，最好還是用存儲過程，這里寫了兩個例子，一個真對sql2000,一個真對sql2005

sql2000
create  PRoc usp_UserGoldHistoryByDateRange
    (
    @StartDate        varchar(10),
    @EndDate        varchar(10),
    @PageSize        int,
    @PageIndex        int,
    @RowCount        int out
    )
as
declare @StartRow    int
declare @EndRow    int
-- 計算當前頁開始行
set @StartRow = (@PageIndex - 1) * @PageSize + 1
-- 計算當前頁結(jié)束行
set @EndRow = @StartRow + @PageSize - 1

-- 建一張內(nèi)存表用于存儲檢索結(jié)果
declare @temp table
    (
    AutoID     [int] IDENTITY (1, 1) NOT NULL,
    RowID    [int]
    )
-- 執(zhí)行檢索
insert into @temp(RowID)
select RowID from UserGoldHistory
where left(DateTimeTag,10) between @StartDate and @EndDate

set @RowCount = @@ROWCOUNT

select * from UserGoldHistory where RowID in (
select RowID from @temp where AutoID between @StartRow and @EndRow)

sql2005
create proc proc_Split
  -- 設(shè)置每頁的行數(shù)
  @page_size int,
  -- 設(shè)置當前頁
  @page_current int,
  -- 總記錄數(shù)
  @rows_count int out
as

select @rows_count=count(UserName) from ForumUser

DECLARE @start_row_num int
DECLARE @end_row_num int
-- 設(shè)置開始行號
SET @start_row_num = (@page_current - 1) * @page_size + 1
-- 設(shè)置結(jié)束行號
SET @end_row_num = @start_row_num + @page_size - 1;

WITH temptesttable AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY UserName) AS row_number, *
  FROM ForumUser
)
SELECT * from temptesttable
WHERE row_number BETWEEN @start_row_num AND @end_row_num

posted on 2006-12-09 19:22 來問（zljGood@hotmail.com） 閱讀(517) 評論(4)  編輯 收藏 引用 網(wǎng)摘 所屬分類: SQL技術(shù)

 
評論
# re: 分頁查詢的一個幫助類 2006-12-10 11:03 THIN
SQL語句何必要用臨時表呢，要是要第100頁呢，不是要先把幾千條數(shù)據(jù)查出來？
子查詢查出ID就行了吧  回復  更多評論   

# re: 分頁查詢的一個幫助類 2006-12-10 13:19 S.Sams
數(shù)據(jù)一多,性能方面還是得考慮  回復  更多評論   

# re: 分頁查詢的一個幫助類 2006-12-10 16:35 來問（zljGood@hotmail.com）
@THIN

當然，要想真正提高查詢效率，最好還是用存儲過程，這里寫了兩個例子
多謝

sql2000
create proc usp_UserGoldHistoryByDateRange
(
@StartDate varchar(10),
@EndDate varchar(10),
@PageSize int,
@PageIndex int,
@RowCount int out
)
as
declare @StartRow int
declare @EndRow int
-- 計算當前頁開始行
set @StartRow = (@PageIndex - 1) * @PageSize + 1
-- 計算當前頁結(jié)束行
set @EndRow = @StartRow + @PageSize - 1

-- 建一張內(nèi)存表用于存儲檢索結(jié)果
declare @temp table
(
AutoID [int] IDENTITY (1, 1) NOT NULL,
RowID [int]
)
-- 執(zhí)行檢索
insert into @temp(RowID)
select RowID from UserGoldHistory
where left(DateTimeTag,10) between @StartDate and @EndDate

set @RowCount = @@ROWCOUNT

select * from UserGoldHistory where RowID in (
select RowID from @temp where AutoID between @StartRow and @EndRow)

sql2005
create proc proc_Split
-- 設(shè)置每頁的行數(shù)
@page_size int,
-- 設(shè)置當前頁
@page_current int,
-- 總記錄數(shù)
@rows_count int out
as

select @rows_count=count(UserName) from ForumUser

DECLARE @start_row_num int
DECLARE @end_row_num int
-- 設(shè)置開始行號
SET @start_row_num = (@page_current - 1) * @page_size + 1
-- 設(shè)置結(jié)束行號
SET @end_row_num = @start_row_num + @page_size - 1;

WITH temptesttable AS
(
SELECT ROW_NUMBER() OVER(ORDER BY UserName) AS row_number, *
FROM ForumUser
)
SELECT * from temptesttable
WHERE row_number BETWEEN @start_row_num AND @end_row_num
http://www.cnblogs.com/laiwen/archive/2006/12/09/587472.html