如何使用公共表表达式(CTE)创建递归计数器

Question

我正在建立一个论坛。现在我想创建一个查询，返回每个帖子上的反应数量。反应定义为某一帖子下的所有帖子。我想用CTE来实现这一点。

现在我得到了这个:

;WITH Reaction(Cnt, ParentId) AS 
(
    SELECT  COUNT(*), ParentId
    FROM   dbo.Post
    GROUP  BY ParentId
)
SELECT ISNULL(Cnt, 0), Post.*
FROM   dbo.Post Post
       LEFT JOIN Reaction
         ON Reaction.ParentId = Post.PostId 

这里列出了所有的"直接"帖子。现在我要让这个查询计数整个树，但我卡住了。我一直在阅读一些关于CTE的内容，我知道你可以进行递归查询，但我不知道如何解决创建计数递归查询的问题。

Answer 1

• 先建立post list
• 聚合后
• 不需要在递归子句中使用LEFT JOIN
• 需要在下一层的递归子句中获得ParentID，否则您只需将PostId循环到同一层的PostId

像这样:

;WITH Reaction AS 
(
    SELECT ParentId
    FROM dbo.Post
    UNION ALL 
    SELECT P.ParentId
    FROM dbo.Post P JOIN Reaction R ON R.ParentId = P.PostId
)
SELECT COUNT(*), ParentId FROM Reaction GROUP BY ParentId;

编辑，更新为行内计数

;WITH Reaction AS
(
   SELECT ParentId FROM dbo.Post 
   UNION ALL 
   SELECT P.ParentId FROM dbo.Post P JOIN Reaction R ON R.ParentId = P.PostId
)
SELECT 
     COUNT(*) OVER (PARTITION BY Reactions.ParentId) AS Cnt,
     * 
FROM Post LEFT JOIN Reaction ON Reaction.ParentId = Post.PostId

Answer 2

我最终使用双CTE来实现计数(基于@gbn的答案)。我需要这个，因为我有一个XML字段需要列出，所以我不能使用GROUP BY .

;WITH Reaction AS 
(
    SELECT ParentId
    FROM dbo.Post
    WHERE ParentId IS NOT NULL
    UNION ALL 
    SELECT P.ParentId
    FROM dbo.Post P JOIN Reaction R ON R.ParentId = P.PostId
    WHERE P.ParentId IS NOT NULL
), 
Reactions AS
(
    SELECT COUNT(*) as CNT, ParentId 
    FROM Reaction 
    GROUP BY ParentId
)
SELECT ISNULL(Reactions.CNT, 0), * 
FROM Post LEFT JOIN Reactions 
    ON Reactions.ParentId = Post.PostId