一条SQL语句搞定SQLServer分页的几种方式

适用于SQL2000/2005

 

SELECT TOP 页大小 *      
FROM table1      
WHERE id NOT IN      
          (      
          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id      
          )      
ORDER BY id 

 

适用2000/2005

 

SELECT TOP 页大小 *      
FROM table1      
WHERE id >     
          (      
          SELECT ISNULL(MAX(id),0)       
          FROM       
                (      
                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id      
                ) A      
          )      
ORDER BY id     

 

 

适用2005

 

SELECT TOP 页大小 *       
FROM       
        (      
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1      
        ) A      
WHERE RowNumber > 页大小*(页数-1)    

 

 

 

---處理時候用代碼增加個ID INT IDENTITY
--方法1：
--适用于 SQL Server 2000/2005
SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN
          (
          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
          )
ORDER BY id

--方法2：
--适用于 SQL Server 2000/2005
SELECT TOP 页大小 *
FROM table1
WHERE id >
          (
          SELECT ISNULL(MAX(id),0) 
          FROM 
                (
                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
                ) A
          )
ORDER BY id

--方法3：
--适用于 SQL Server 2005
SELECT TOP 页大小 * 
FROM 
        (
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
        ) A
WHERE RowNumber > 页大小*(页数-1)

--方法四 适用于 SQL Server 2005
DECLARE @pagenum AS INT, @pagesize AS INT 
SET @pagenum = 2 
SET @pagesize = 3 
SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum, 
        newsid, topic, ntime, hits 
      FROM news) AS D 
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize 
ORDER BY newsid DESC