34,576
社区成员
发帖
与我相关
我的任务
分享
查询第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
--参考....
--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
使用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排序就快,按其他字段排序就慢。