使用SQL Server 2005中新增的ROW_NUMBER 几种写法分别如下: SELECT TOP 20 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber, * FROM dbo.mem_member) _myResults WHERE RowNumber > 10000 SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber, * FROM dbo.mem_member) _myResults WHERE RowNumber between 10000 and 10020 WITH OrderedResults AS (SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member) SELECT * FROM OrderedResults WHERE RowNumber between 10000 and 10020 不管哪种写法,性能都不理想。在8,9万条数据的情况下要运行6秒左右。 2、使用临时表再加存储过程 BEGIN DECLARE @PageLowerBound int DECLARE @PageUpperBound int
-- Set the page bounds SET @PageLowerBound = 10000 SET @PageUpperBound = 10020 -- Create a temp table to store the select results Create Table #PageIndex ( [IndexId] int IDENTITY (1, 1) NOT NULL, [Id] varchar(18) )
-- Insert into the temp table declare @SQL as nvarchar(4000) SET @SQL = 'INSERT INTO #PageIndex (Id)' SET @SQL = @SQL + ' SELECT' SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound) SET @SQL = @SQL + ' m_id' SET @SQL = @SQL + ' FROM dbo.mem_member' SET @SQL = @SQL + ' ORDER BY NameC'
-- Populate the temp table exec sp_executesql @SQL -- Return paged results SELECT O.* FROM dbo.mem_member O, #PageIndex PageIndex WHERE PageIndex.IndexID > @PageLowerBound AND O.[m_Id] = PageIndex.[Id] ORDER BY PageIndex.IndexID
drop table #PageIndex END 而使用这种方法,在同样的情况下用时只需1秒。 看样子,row_number是个鸡肋。 3、如果觉得临时表不好,还可以使用SET ROWCOUNT begin DECLARE @first_id varchar(18), @startRow int
SET ROWCOUNT 10000 SELECT @first_id = m_id FROM mem_member ORDER BY m_id SET ROWCOUNT 20 SELECT m.* FROM mem_member m WHERE m_id >= @first_id ORDER BY m.m_id SET ROWCOUNT 0 end 不过,这种方法有缺点。按ID排序就快,按其他字段排序就慢。
--参考.... --sql server中分页获取数据的存储过程 CREATE PROCEDURE dbo.xp_GetPage ( @sql varchar(1000), @page int = 1, @pageSize int = 20, @needCount bit = 1 ) AS SET NOCOUNT ON DECLARE @P1 int --P1是游标的id
查询第X页,每页Y条记录最基本的处理方法(原理):如果表中有主键(记录不重复的字段也可以),可以用类似下面的方法,当然y,(x-1)*y要换成具体的数字,不能用变量:select top y * from 表 where 主键 not in(select top (x-1)*y 主键 from 表)如果表中无主键,可以用临时表,加标识字段解决.这里的x,y可以用变量.select id=identity(int,1,1),* into #tb from 表 select * from #tb where id between (x-1)*y and x*y-1
DECLARE @i SMALLINT SET @i = 1 WHILE (@i <=100) BEGIN INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) ) INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) ) INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29) ) SET @i = @i + 1 END
ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
*
FROM
dbo.mem_member) _myResults
WHERE
RowNumber > 10000 SELECT * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
*
FROM
dbo.mem_member) _myResults
WHERE
RowNumber between 10000 and 10020
WITH OrderedResults AS (SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member) SELECT * FROM OrderedResults WHERE RowNumber between 10000 and 10020
不管哪种写法,性能都不理想。在8,9万条数据的情况下要运行6秒左右。
2、使用临时表再加存储过程
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = 10000
SET @PageUpperBound = 10020 -- Create a temp table to store the select results
Create Table #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[Id] varchar(18)
)
-- Insert into the temp table
declare @SQL as nvarchar(4000)
SET @SQL = 'INSERT INTO #PageIndex (Id)'
SET @SQL = @SQL + ' SELECT'
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
SET @SQL = @SQL + ' m_id'
SET @SQL = @SQL + ' FROM dbo.mem_member'
SET @SQL = @SQL + ' ORDER BY NameC'
-- Populate the temp table
exec sp_executesql @SQL -- Return paged results
SELECT O.*
FROM
dbo.mem_member O,
#PageIndex PageIndex
WHERE
PageIndex.IndexID > @PageLowerBound
AND O.[m_Id] = PageIndex.[Id]
ORDER BY
PageIndex.IndexID
drop table #PageIndex
END
而使用这种方法,在同样的情况下用时只需1秒。 看样子,row_number是个鸡肋。 3、如果觉得临时表不好,还可以使用SET ROWCOUNT
begin
DECLARE @first_id varchar(18), @startRow int
SET ROWCOUNT 10000
SELECT @first_id = m_id FROM mem_member ORDER BY m_id SET ROWCOUNT 20 SELECT m.*
FROM mem_member m
WHERE m_id >= @first_id
ORDER BY m.m_id SET ROWCOUNT 0
end
不过,这种方法有缺点。按ID排序就快,按其他字段排序就慢。
--sql server中分页获取数据的存储过程
CREATE PROCEDURE dbo.xp_GetPage
(
@sql varchar(1000),
@page int = 1,
@pageSize int = 20,
@needCount bit = 1
)
AS
SET NOCOUNT ON
DECLARE @P1 int --P1是游标的id
SET @page = (@page-1) * @pagesize + 1
EXEC sp_cursoropen @P1 output,@sql
EXEC sp_cursorfetch @P1, 16, @page, @pagesize
EXEC sp_cursorclose @P1
--上面的就够了,下面的代码是为了统计总记录条数
select @RowCount
GO
查询第X页,每页Y条记录最基本的处理方法(原理):如果表中有主键(记录不重复的字段也可以),可以用类似下面的方法,当然y,(x-1)*y要换成具体的数字,不能用变量:select top y * from 表 where 主键 not in(select top (x-1)*y 主键 from 表)如果表中无主键,可以用临时表,加标识字段解决.这里的x,y可以用变量.select id=identity(int,1,1),* into #tb from 表
select * from #tb where id between (x-1)*y and x*y-1