sql - 有没有一种方法可以从 SQL 查询中获取总数？

Question

这是我的查询:

SELECT CG.Id, CG.Name, CG.IsToggled, COUNT(*) AS TotalWordCount
FROM CategoryGroup AS CG
JOIN Category AS C ON CG.Id = C.GroupId
JOIN Phrase AS P ON C.Id = P.CategoryId
GROUP BY CG.Id
ORDER BY CG.Name

我需要做的是获取 TotalWordCount 列的总数，其中 CG.IsToggled = 1

这是否可以在同一个查询中实现，或者我可以通过两个查询实现这一点。如果两个 SQL 语句确实得出与所有 TotalWordCount 加在一起的值相同的值，那就没问题了。

Answer 1

你可以用 window function 来解决这个问题

SELECT CG.Id, CG.Name, CG.IsToggled, COUNT(*) AS TotalWordCount,
    COUNT(*) OVER (PARTITION BY CG.id) as TotalTotalWordCount
FROM CategoryGroup AS CG
JOIN Category AS C ON CG.Id = C.GroupId
JOIN Phrase AS P ON C.Id = P.CategoryId
GROUP BY CG.Id
ORDER BY CG.Name

假设这实际上是 Microsoft SQL Server(这意味着发布的原始查询会出错，因为它没有 CG.Name 在您的 GROUP BY 中)。