如何在单独的数据集中获取CTE的行数？ [英] How do I get rowcount of a cte in a separate dataset?

问题描述

我确定了一种使用CTE和Row_Number函数从数据库中获取快速分页结果的方法，如下所示...

I have identified a way to get fast paged results from the database using CTEs and the Row_Number function, as follows...

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

使用此技术返回即使在复杂的联接和过滤器上，结果也确实非常快。

Using this technique the returned results is really really fast even on complex joins and filters.

要执行分页，我需要知道完整CTE返回的总行数。我通过放入保存它的列来合并

To perform paging I need to know the Total Rows returned by the full CTE. I have "Bodged" this by putting a column that holds it

Total = ( SELECT
              Count( id )
            FROM   cteCustomers )

有没有更好的方法可以返回不同结果集中的总数而无需合并成一列？因为它是CTE，所以我似乎无法将其纳入第二个结果集中。

Is there a better way to return the total in a different result set without bodging it into a column? Because it's a CTE I can't seem to get it into a second result set.

推荐答案

首先不使用临时表，我将使用CROSS JOIN来减少对行进行逐行求值的风险COUNT

Without using a temp table first, I'd use a CROSS JOIN to reduce the risk of row by row evaluation on the COUNT

要获得总行数，需要在WHERE上单独进行

To get total row, this needs to happen separately to the WHERE

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 ASC

但是，不能保证给出此处所示的准确结果：

我可以从sql服务器中的一个sql查询中获取count（）和行吗？

However, this isn't guaranteed to give accurate results as demonstrated here:
can I get count() and rows from one sql query in sql server?

编辑：发表一些评论。

如何避免交叉联接

WITH 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

注意：YMMV的性能取决于2005或2008，Service Pack等

Note: YMMV for performance depending on 2005 or 2008, Service pack etc

编辑2：

SQL Server Central 显示了另一种可以逆转的技术ROW_NUMBER。看起来很有用

SQL Server Central shows another technique where you have reverse ROW_NUMBER. Looks useful

这篇关于如何在单独的数据集中获取CTE的行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！