SQL Server 2005下的分页SQL 其实基本上有三种方法: 1SELECT TOP 20 * FROM (SELECT
2 ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber, 3 * 4FROM 5 dbo.mem_member) _myResults 6WHERE 7 RowNumber > 10000 8
1SELECT * FROM (SELECT
2 ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber, 3 * 4FROM 5 dbo.mem_member) _myResults 6WHERE 7 RowNumber between 10000 and 10020
1WITH OrderedResults AS
2 3(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member) 4 5SELECT * 6 7FROM OrderedResults 8 9WHERE RowNumber between 10000 and 10020 不管哪种写法,性能都不理想。在8,9万条数据的情况下要运行6秒左右。 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秒。 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排序就快,按其他字段排序就慢。 |
|
来自: WindySky > 《sql server》