logo标签列表

带分页和计数的SQL Server查询

sql-serverpaginationcommon-table-expression
37

我想要使用分页的方式进行数据库查询。因此，我使用了一个通用表达式和排名函数来实现这一点。请看下面的示例。

declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');

declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;

with query as
(
    select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name from query
    where line > (@pagenumber - 1) * @pagesize

在这里，我可以指定@pagesize和@pagenumber变量，以便只获取我想要的记录。但是，这个例子（来自存储过程）用于在Web应用程序中制作网格分页。这个Web应用程序需要显示页面数字。例如，如果数据库中有12条记录，页面大小为3，则我将不得不显示4个链接，每个链接代表一页。

但是，如果不知道有多少条记录，就无法实现这一点，而这个例子只提供记录的子集。

然后我更改了存储过程以返回count(*)。

declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;
with query as
(
    select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line, total = count(*) over()from @table
)
select top (@pagesize) name, total from query
    where line > (@pagenumber - 1) * @pagesize

所以，除每一行外，它还将显示记录的总数。但我不喜欢这样。

我的问题是是否有更好的方法（性能），可以在不返回SELECT中的此信息的情况下设置@total变量。或者，这个总列是否会对性能造成太大影响？

谢谢。

6个回答
67

假设您使用 MSSQL 2012，您可以使用 Offset 和 Fetch 大大简化服务器端分页。我们发现性能良好，在大多数情况下更好。至于获取总列数，只需在以下窗口函数中使用它即可……它不会包括 'offset' 和 'fetch' 强制执行的限制。

对于 Row_Number，您可以像之前那样使用窗口函数，但我建议您将其在客户端按如下方式计算：(pagenumber*pagesize + resultsetRowNumber)，这样如果您在 10 个结果的第 5 页上且在第三行，您将输出第 53 行。

当应用于具有约 200 万订单的订单表时，我发现以下结果：

快速版本

此运行时间少于一秒钟。 它的好处在于您可以在公共表达式中进行筛选，这既适用于分页进程又适用于计数。 当 where 子句中有许多谓词时，这使事情保持简单。

declare @skipRows int = 25,
        @takeRows int = 100,
        @count int = 0

;WITH Orders_cte AS (
    SELECT OrderID
    FROM dbo.Orders
)

SELECT 
    OrderID,
    tCountOrders.CountOrders AS TotalRows
FROM Orders_cte
    CROSS JOIN (SELECT Count(*) AS CountOrders FROM Orders_cte) AS tCountOrders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

慢速版本

这大约花了10秒钟的时间，而是Count(*)导致了缓慢。我很惊讶这么慢，但我怀疑它只是为每一行计算总数。虽然非常简洁。

declare @skipRows int = 25,
@takeRows int = 100,
@count int = 0


SELECT 
    OrderID,
    Count(*) Over() AS TotalRows
FROM Location.Orders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

结论

我们之前进行过性能优化的过程，实际上发现这取决于查询、谓词和涉及的索引。例如，当我们引入了一个视图时，它会变得很慢，因此我们实际上从基本表查询，然后连接视图（其中包括基本表），这样性能非常好。

我建议采用一些简单的策略，并将其应用于瓶颈查询的高价值查询。

谢谢，我会使用OFFSET和FETCH运算符来改进我的分页查询。然而，我的担忧在于结果集中count(*)列的返回（如果它对性能有很大的影响）。 - Fabio
2
CROSS JOIN比Count(*) Over()快得多！ - Fabio
1
哎呀..我真笨..我得把WHERE子句放在WITH语句的第一个SELECT中...现在运行正常了！谢谢你，你让我今天过得开心 - Arxeiss
3
@FabioGouw，鉴于SQL Server 2014的实际情况，我们发现使用带有交叉连接的CTE比使用OVER()要慢。在处理包含8万条记录的数据集时，使用CTE的速度大约比使用OVER()慢25%。 - DuncanMack
1
（更正--SQL Server 2012，而不是2014年。）针对楼主的观点，我认为因各种因素而异。 - DuncanMack
显示剩余2条评论
2
DECLARE @pageNumber INT = 1  , 
        @RowsPerPage INT = 20

SELECT  *
FROM    TableName
ORDER BY Id
        OFFSET ( ( @pageNumber - 1 ) * @RowsPerPage ) ROWS
             FETCH NEXT @RowsPerPage ROWS ONLY;
8
"Where is count?" 的翻译是什么地方有计数？ - Amirhossein
1
@pagenumber=5
@pagesize=5

创建一个通用表达式，并编写如下逻辑。
Between ((@pagenumber-1)*(@pagesize))+1 and (@pagenumber *@pagesize)
1
如果事先计算数量会怎样？
declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;

SELECT @total = count(*)
FROM @table

with query as
(
   select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name, @total total from query
where line > (@pagenumber - 1) * @pagesize

另一种方法是计算max(line)。查看链接 当使用ROW_NUMBER时从SQL Server返回总记录数 更新： 对于单个查询，请查看上面链接中marc_s的答案。
    with query as
    (
       select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
    )
    select top (@pagesize) name, 
       (SELECT MAX(line) FROM query) AS total 
    from query
    where line > (@pagenumber - 1) * @pagesize
1
谢谢，但是在这里我必须查询数据库两次：第一次是为了计数，第二次是为了实际的分页结果集。对于我的示例，它可能有效，但是真正的存储过程具有复杂的查询，包括连接和可选参数，我不想重写它并执行两次。 - Fabio
1
在另一个问题中，有一条评论指出“用Count(*)替换Max(RowNum)字段”，这导致了我提出这个问题的相同问题。但是@BlackjacketMack已经将这种方法与交叉连接进行了比较，后者具有更好的性能。谢谢。 - Fabio
0

我们可以通过多种方式实现分页：希望这些信息对您和其他人有用。

示例1：使用offset-fetch next子句。2005年引入。

declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');

declare @pagenumber int = 1
declare @pagesize int = 3

--this is a CTE( common table expression and this is introduce in 2005)
with query as
(
  select ROW_NUMBER() OVER(ORDER BY name ASC) as line, name from @table
) 

--order by clause is required to use offset-fetch
select * from query
order by name 
offset ((@pagenumber - 1) * @pagesize) rows
fetch next @pagesize rows only

示例2：使用row_number()函数和between

declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');

declare @pagenumber int = 2
declare @pagesize int = 3

SELECT *
FROM 
(select ROW_NUMBER() OVER (ORDER BY PRODUCTNAME) AS RowNum, * from Products)
as Prodcut
where RowNum between (((@pagenumber - 1) * @pageSize )+ 1) 
and (@pagenumber * @pageSize )

希望这些对大家有所帮助。

0

我不喜欢其他过于复杂的解决方案，所以这是我的版本。

一次执行三个查询并使用输出参数获取计数值。此查询返回总计数、筛选计数和页面行。它支持对源数据进行排序、搜索和筛选。易于阅读和修改。

假设您有两个具有一对多关系的表，项目和它们的价格随时间变化，因此示例查询并不太简单。

create table shop.Items
(
    Id uniqueidentifier not null primary key,
    Name nvarchar(100) not null,
);

create table shop.Prices
(
    ItemId uniqueidentifier not null,
    Updated datetime not null,
    Price money not null,
    constraint PK_Prices primary key (ItemId, Updated),
    constraint FK_Prices_Items foreign key (ItemId) references shop.Items(Id)
);

这是查询语句：

select @TotalCount = count(*) over()
from shop.Items i;

select @FilterCount = count(*) over()
from shop.Items i
outer apply (select top 1 p.Price, p.Updated from shop.Prices p where p.ItemId = i.Id order by p.Updated desc) as p
where (@Search is null or i.Name like '%' + @Search + '%')/**where**/;

select i.Id as ItemId, i.Name, p.Price, p.Updated
from shop.Items i
outer apply (select top 1 p.Price, p.Updated from shop.Prices p where p.ItemId = i.Id order by p.Updated desc) as p
where (@Search is null or i.Name like '%' + @Search + '%')/**where**/
order by /**orderby**/i.Id
offset @SkipCount rows fetch next @TakeCount rows only;

您需要向查询提供以下参数：

  • @SkipCount - 从页码计算跳过多少条记录。
  • @TakeCount - 返回多少条记录，从页面大小计算或等于页面大小。
  • @Search - 在某些列中搜索的文本，由网格搜索框提供。
  • @TotalCount - 数据源中记录的总数，输出参数。
  • @FilterCount - 搜索和过滤操作后的记录数，输出参数。

如果网格必须支持按列排序行，则可以将/**orderby**/注释替换为列及其排序方向列表。您可以从网格获取此信息并将其转换为SQL表达式。我们仍然需要最初按某一列对记录进行排序，我通常使用ID列进行排序。

如果网格必须支持单独按每个列筛选数据，则可以将/**where**/注释替换为该SQL表达式。

如果用户不搜索和过滤数据，而只是通过网格页面点击，那么此查询不会改变，数据库服务器会非常快速地执行它。

你是否曾经见过长度为5页的SQL查询语句？ - Victor Zakharov
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接