将 SQL 的 OFFSET 和 FETCH 与窗口函数结合使用，实现高效分页和总计数

在现代应用程序中，高效的数据检索对于无缝的用户体验至关重要。一种常见的场景是对结果进行分页，其中显示记录的子集，并且用户可以导航到后续页面以查看更多内容。此外，显示记录总数通常很有用，可以让用户了解数据集的大小。

SQL 提供了强大的工具来实现此目的，在本文中，我们将探讨如何将OFFSETandFETCH子句与窗口函数结合使用来对结果进行分页并在单个查询中获取总计数。

问题

考虑一个场景，您正在为公司的资源构建仪表板。您想要显示资源列表、与每个资源关联的项目数量以及资源总数。收获是什么？您只想在每页显示有限数量的资源，但仍然希望显示资源总数。

一种简单的方法可能涉及两个单独的查询：一个用于获取分页结果，另一个用于获取总计数。这是低效的并且会给数据库带来压力，尤其是对于大型数据集。

解决方案

解决方案在于 SQL 的窗口函数和OFFSETandFETCH子句。以下是所提供的 SQL 代码的细分：

1. 总计数的窗口函数：该行NbTotalResources = COUNT(*) OVER()使用COUNT窗口函数来获取资源总数。该OVER()子句确保在整个结果集上计算计数，而不仅仅是分页子集。
2. 排序和分页：该ORDER BY子句按资源键对资源进行排序。andOFFSET子句FETCH处理分页。OFFSET跳过指定的行数，并FETCH限制返回的行数。

代码示例：

SELECT  dbo.Resources.ID as ResourceID,
        dbo.Resources.CompanyID as CompanyID,
        dbo.Resources.ResourceKey as ResourceKey,
        count(dbo.ProjectResources.ID) as NbProjects,
        NbTotalResources = COUNT(*) OVER() 

FROM dbo.Resources (NOLOCK)
    LEFT JOIN dbo.ProjectResources (NOLOCK) ON (dbo.ProjectResources.ResourceID = dbo.Resources.ID )
WHERE dbo.Resources.CompanyID = @companyid
 AND (@searchQuery='' OR dbo.Resources.ResourceKey like '%' + @searchQuery + '%')
GROUP BY dbo.Resources.ID,
        dbo.Resources.CompanyID,
        dbo.Resources.ResourceKey
ORDER BY dbo.Resources.ResourceKey
OFFSET @OffsetX ROWS 
FETCH NEXT @MaxRows ROWS ONLY;

结论

通过利用 SQL 的强大功能，开发人员可以有效地对结果进行分页并在单个查询中获取聚合数据。这不仅减少了数据库调用的数量，而且还确保应用程序保持高性能和响应能力。