SQL常用分頁的辦法

表中主鍵必須為識別欄位，[ID] int IDENTITY (1,1) 
1.分頁方案一：(利用Not In和SELECT TOP分頁) 

語句形式：  
SELECT TOP 頁記錄數量 * 
FROM 表名 
WHERE (ID NOT IN 
  (SELECT TOP (每頁行數*(頁數-1)) ID 
  FROM 表名 
  ORDER BY ID)) 
  ORDER BY ID 
//自己還可以加上一些查詢條件 

例: 
select top 2 * 
from Sys_Material_Type 
where (MT_ID not in 
    (select top (2*(3-1)) MT_ID from Sys_Material_Type  order by MT_ID)) 
order by MT_ID 

 

又例：

 

SELECT TOP 20 * 

FROM capm 

WHERE (stkid NOT IN 

  (SELECT TOP (100*2) stkid 

  FROM capm 

  ORDER BY stkid)) 

  ORDER BY stkid 

2.分頁方案二：(利用ID大於多少和SELECT TOP分頁） 

語句形式： 
SELECT TOP 每頁記錄數量 * 
FROM 表名 
WHERE (ID > 
          (SELECT MAX(id) 
    FROM (SELECT TOP 每頁行數*頁數 id  FROM 表 
          ORDER BY id) AS T) 
      ) 
ORDER BY ID 

例: 
SELECT TOP 2 * 
FROM Sys_Material_Type 
WHERE (MT_ID > 
          (SELECT MAX(MT_ID) 
          FROM (SELECT TOP (2*(3-1)) MT_ID 
                FROM Sys_Material_Type 
                ORDER BY MT_ID) AS T)) 
ORDER BY MT_ID 

 

 

 

3.分頁方案三：(利用SQL的遊標預存程序分頁) 
create  procedure SqlPager 
@sqlstr nvarchar(4000), --查詢字串 
@currentpage int, --第N頁 
@pagesize int --每頁行數 
as 
set nocount on 
declare @P1 int, --P1是遊標的id 
@rowcount int 
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output 
select ceiling(1.0*@rowcount/@pagesize) as 總頁數--,@rowcount as 總行數,@currentpage as 當前頁 
set @currentpage=(@currentpage-1)*@pagesize+1 
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1 
set nocount off 

4.總結: 
其它的方案：如果沒有主鍵，可以用暫存資料表，也可以用方案三做，但是效率會低。 
建議最佳化的時候，加上主鍵和索引，查詢效率會提高。 

通過SQL 查詢分析器，顯示比較：我的結論是: 
分頁方案二：(利用ID大於多少和SELECT TOP分頁）效率最高，需要拼接SQL語句 
分頁方案一：(利用Not In和SELECT TOP分頁)  效率次之，需要拼接SQL語句 
分頁方案三：(利用SQL的遊標預存程序分頁)    效率最差，但是最為通用

 

 

-------------------------------------------------------------------------------------------

 

另一個角度：

 

1、使用SQL Server 2005中新增的ROW_NUMBER

幾種寫法分別如下：

1SELECT TOP 20 * FROM (SELECT   ROW_NUMBER() OVER (ORDER BY Namec)

2AS RowNumber,  * FROM  dbo.mem_member) T  WHERE   RowNumber > 10000

例子：

SELECT TOP 100 * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY stkid) AS RowNumber,* FROM  capm) A

WHERE   RowNumber > 0

 

 

1SELECT * FROM (SELECT
2   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3   * FROM  dbo.mem_member) _myResults
4WHERE
5  RowNumber between 10000 and 10020

 

 

1WITH OrderedResults AS 
2(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member)
3SELECT * FROM OrderedResults
4WHERE RowNumber between 10000 and 10020

不管哪種寫法，效能都不理想。在8,9萬條資料的情況下要運行6秒左右

 

 

 

 

2、使用暫存資料表再加預存程序

 1BEGIN
 2                DECLARE @PageLowerBound int
 3                DECLARE @PageUpperBound int
 4                
 5                -- Set the page bounds
 6                SET @PageLowerBound = 10000
 7                SET @PageUpperBound = 10020
 8
 9                -- Create a temp table to store the select results
10                Create Table #PageIndex
11                (
12                    [IndexId] int IDENTITY (1, 1) NOT NULL,
13                    [Id] varchar(18) 
14                )
15                
16                -- Insert into the temp table
17                declare @SQL as nvarchar(4000)
18                SET @SQL = 'INSERT INTO #PageIndex (Id)'
19                SET @SQL = @SQL + ' SELECT'
20                SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
21                SET @SQL = @SQL + ' m_id'
22                SET @SQL = @SQL + ' FROM dbo.mem_member'
23                SET @SQL = @SQL + ' ORDER BY NameC'
24                
25                -- Populate the temp table
26                exec sp_executesql @SQL
27
28                -- Return paged results
29                SELECT O.*
30                FROM
31                    dbo.mem_member O,
32                    #PageIndex PageIndex
33                WHERE
34                    PageIndex.IndexID > @PageLowerBound
35                    AND O.[m_Id] = PageIndex.[Id]
36                ORDER BY
37                    PageIndex.IndexID
38                
39drop table #PageIndex            
40                END

而使用這種方法，在同樣的情況下用時只需1秒。

看樣子，row_number是個雞肋。

 

3、如果覺得暫存資料表不好，還可以使用SET ROWCOUNT

 1begin
 2DECLARE @first_id varchar(18), @startRow int
 3    
 4SET ROWCOUNT 10000
 5SELECT @first_id = m_id FROM mem_member ORDER BY m_id
 6
 7SET ROWCOUNT 20
 8
 9SELECT m.* 
10FROM mem_member m
11WHERE m_id >= @first_id
12ORDER BY m.m_id
13
14SET ROWCOUNT 0
15end

不過，這種方法有缺點。按ID排序就快，按其他欄位排序就慢