使用CTE和UNION ALL进行查询，并在底部显示总计

Question

这是我正在尝试的SQL Server查询的简化版本或代表：

WITH T1 AS (
SELECT DocNum, CardCode, CardName FROM OINV
)
SELECT CardName AS 'Customer Name', DocNum FROM T1
UNION ALL
SELECT 'Grand Total', COUNT(DocNum) FROM T1
ORDER BY "Customer Name"

在实际查询中，我无法避免使用CTE，因为我需要在同一查询中的另一个CTE中引用一个CTE的结果，并且存在多个CTE。

我的主要要求是在查询的末尾有一个"总计"行。总计行将显示一些汇总数字，如计数、总和等。在实际查询中，总计行本身将根据CTE结果之一导出其汇总数字。

在上面的简化查询中，如何在查询结果中不添加任何额外列的情况下在查询底部实现总计。

在我的真实查询中，第一个CTE获得了所有文件的列表及其未偿余额和账龄天数；

第二个查询通过连接其他几个表来添加额外的列，并将未付金额分类到账龄区间，如0-30天、30-60天，依此类推

我需要在第二个查询的结果中添加一个"总计"行，该行应该提供所有客户的未偿总额以及CTE2中分类的每个账龄区间的总数。

Answer 1

您通常会在前端计算出您的总计等。

但是，如果你必须这样做，为什么当你从CTE中选择时不能使用子查询，例如

WITH T1 AS (
SELECT DocNum, CardCode, CardName
FROM OINV
)
SELECT [Customer Name], DocNum
FROM
SELECT CardName AS [Customer Name], DocNum, 0 OrderBy
FROM T1
UNION ALL
SELECT 'Grand Total', COUNT(DocNum), 1 OrderBy
FROM T1
) X
ORDER BY OrderBy ASC, [Customer Name];

注意：不要用单引号来表示列名。使用双引号或方括号。

Answer 2

您可以使用GROUPING SETS，并且只需要对基表/CTE 进行一次扫描

WITH T1 AS (
SELECT DocNum, CardCode, CardName
FROM OINV
)
SELECT
CASE WHEN GROUPING(CardName) = 0 THEN CardName ELSE 'GrandTotal' END AS CardName,
CASE WHEN GROUPING(CardName) = 0 THEN DocNum   ELSE CAST(COUNT(*) AS varchar(30)) END AS DocNum
FROM T1
GROUP BY GROUPING SETS (
(CardName, DocNum),
()
)
ORDER BY
GROUPING(CardName),
CardName;