如何在单独的数据集中获取CTE的行数？
|
我确定了一种使用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
Total = ( SELECT
Count( id )
FROM cteCustomers )
没有找到相关结果
已邀请:
2 个回复
朗排谎纷瘟
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 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 CROSS JOIN (SELECT Count( *) AS Total FROM cteCustomers ) foo WHERE row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) ) ORDER BY row ASCWITH cteCustomers AS ( SELECT id, Row_Number( ) OVER(ORDER BY Age DESC) AS Row, COUNT(*) OVER () AS Total --the magic for this edit FROM @Customer WHERE employed = 1 /*Imagine I\'ve joined to loads more tables with a really complex where clause*/ ) SELECT name, age, Total 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亲奋漏
WITH cteCustomers AS ( SELECT id, Row_Number() OVER(ORDER BY Age DESC) AS Row, Row_Number() OVER(ORDER BY id ASC) + Row_Number() OVER(ORDER BY id DESC) - 1 AS Total /*<- voodoo here*/ FROM @Customer WHERE employed = 1 /*Imagine I\'ve joined to loads more tables with a really complex where clause*/ ) SELECT name, age, Total /*This is where I choose the columns I want to read, it returns really fast!*/ FROM cteCustomers INNER JOIN @Customer cust ON cust.id = cteCustomers.id WHERE row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) ) ORDER BY row ASC