我已经找到了一种使用 CTE 和 Row_Number 函数从数据库获取快速分页结果的方法,如下所示......
DECLARE @PageSize INT = 1
DECLARE @PageNumber INT = 2
DECLARE @Customer TABLE (
ID INT IDENTITY(1, 1),
Name VARCHAR(10),
age INT,
employed BIT)
INSERT INTO @Customer
(name,age,employed)
SELECT 'bob',21,1
UNION ALL
SELECT 'fred',33,1
UNION ALL
SELECT 'joe',29,1
UNION ALL
SELECT 'sam',16,1
UNION ALL
SELECT 'arthur',17,0;
WITH cteCustomers
AS ( SELECT
id,
Row_Number( ) OVER(ORDER BY Age DESC) AS Row
FROM @Customer
WHERE employed = 1
/*Imagine I've joined to loads more tables with a really complex where clause*/
)
SELECT
name,
age,
Total = ( SELECT
Count( id )
FROM cteCustomers )
FROM cteCustomers
INNER JOIN @Customer cust
/*This is where I choose the columns I want to read, it returns really fast!*/
ON cust.id = cteCustomers.id
WHERE row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER BY row ASC
使用这种技术,即使在复杂的连接和过滤器上,返回的结果也非常非常快。
要执行分页,我需要知道完整 CTE 返回的总行数。我通过放置一个容纳它的柱子来“固定”它
Total = ( SELECT
Count( id )
FROM cteCustomers )
有没有更好的方法来返回不同结果集中的总计而不将其合并到列中?因为它是 CTE,所以我似乎无法将其放入第二个结果集中。