【改斜归正】 sqlserver sql 分页语句

      在Google上使用“sql 分页”关键字进行搜索，几乎所有的答案都是那三条。其二效率最高，其三使用游标，效率最差。

      下面是那三种方法 （插入代码没有sql选项）

方法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)

      粗略看了，这三种方法都没用错。说的都有理。相对来说前两种方法对于带条件的查询不够方便。

      至于效率，今天实际加了条件测试一下。实际速度并不是方案2最快，方案3最慢。实际情况是方案3最快，方案1次之，方案2最慢。与网上结果截然相反。但是方案3有一个非常要紧的错误：没有排序。直接使用可能会导致错误。

      不加条件的测试结果是一样的。

      方案3的正确用法是：

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

以下是测试方案。

创建下表：

USE [BigDb] GO /****** Object: Table [dbo].[t7] Script Date: 02/20/2010 20:35:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[t7]( [id] [int] IDENTITY(1,1) NOT NULL, [pid] [int] NOT NULL, [sex] [bit] NOT NULL, [name] [nchar](5) COLLATE Chinese_PRC_CI_AS NOT NULL, [age] [int] NOT NULL, [time] [datetime] NOT NULL, [note] [ntext] COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_t8] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

其规模是10^7行数据。

SELECT COUNT(*) FROM [t7] ----------- 10000000 (1 row(s) affected)

分别执行下列代码，记录时间。

DECLARE @time1 INT DECLARE @time2 INT DECLARE @time3 INT DECLARE @begintime DATETIME,@endtime DATETIME DECLARE @count INT --方法1 SET @begintime=getdate() SELECT TOP 20 * FROM t7 WHERE id NOT IN ( SELECT TOP 100000 id FROM t7 WHERE age<20 AND sex=0 ORDER BY id ) AND age<20 AND sex=0 ORDER BY id SET @endtime=getdate() SET @time1=datediff(millisecond,@begintime,@endtime) --方法2 SET @begintime=getdate() SELECT TOP 20 * FROM t7 WHERE id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 100000 id FROM t7 WHERE age<20 AND sex=0 ORDER BY id )A ) AND age<20 AND sex=0 ORDER BY id SET @endtime=getdate() SET @time2=datediff(millisecond,@begintime,@endtime) --方法3 SET @begintime=getdate() SELECT TOP 20 * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS rows,* FROM t7 WHERE age<20 AND sex=0 )A WHERE rows>100000 ORDER BY rows SET @endtime=getdate() SET @time3=datediff(millisecond,@begintime,@endtime) SET @count=(SELECT COUNT(*) FROM [t7]) print '数据量：'+cast(@count AS VARCHAR) print '方法1：'+cast(@time1 AS VARCHAR) print '方法2：'+cast(@time2 AS VARCHAR) print '方法3：'+cast(@time3 AS VARCHAR) 结果： 数据量：10000000 方法1：283 方法2：373 方法3：140

      看到这里你可能会说这个时间差异非常小。我想说的是，这个数据量只是10^7级。并且查询条件并不算复杂，也没有链接表。更复杂的时候，需要更加优化代码。