带有 CTE 的递归查询 - 给定父级的子列的总和 [英] Recursive query with CTE - SUM of child columns for a given parent

问题描述

我有一个论坛数据库，将论坛信息存储在一个列中.论坛允许无限子论坛.

I have a forum database that stores forum information in a single column. The forum allows for unlimited subforums.

表格名称 - 论坛

| ForumID | ParentForumID | Name | Description | TopicCount | ReplyCount | LastPost |

给定一个 ForumID 作为参数，我正在尝试 SUM 所有子条目的 TopicCount 和 ReplyCount.我还试图返回最新的 LastPost，它被指定为 DATETIME.

Given a ForumID as a parameter I am trying to SUM the TopicCount and ReplyCount for all child entries. I am also trying to return the latest LastPost, which is specified as DATETIME.

我在谷歌和这个论坛上搜索过，我知道我应该使用递归 CTE，但在理解语法方面有一些困难.这是我的 CTE - 正在进行中.

I've searched google and this forum and understand I should be using a recursive CTE but am having some difficulty understanding the syntax. Here is my CTE - work in progress.

   WITH CTE (ForumID, ParentForumID)
   AS
   (
       SELECT ForumID AS Descendant, ParentForumID as Ancestor
       FROM forums
       UNION ALL
       SELECT e.Ancestor
       FROM
          CTE as e
          INNER JOIN CTE AS d
          ON Descendant = d.ParentForumID
   )
   SELECT e.Descendant, SUM(TopicCount) AS topics, SUM(ReplyCount) AS replys
   FROM CTE e
   WHERE e.Ancestor = 1

其中 1 = 论坛 ID 参数.

Where 1 = Parameter for the forum ID.

预先感谢您的帮助！

推荐答案

你做得很好 - 你很接近 :-)

You're doing OK - you're quite close :-)

基本上，您需要:

• 定义在 CTE 之前选择的初始论坛
• 创建一个指向该论坛定义的锚点"查询
• 然后遍历所有子节点并总结 TopicCount 和 ReplyCount 计数器

• define the initial forum to be picked before the CTE
• create an "anchor" query to that forum defined
• then iterate over all children and sum up the TopicCount and ReplyCount counters

所以你的代码应该是这样的:

So your code should look something like this:

DECLARE @RootForumID INT
SET @RootForumID = 1  -- or whatever you want...

;WITH CTE AS
(
   -- define the "anchor" query - select the chosen forum
   SELECT 
       ForumID, TopicCount, ReplyCount, LastPost
   FROM 
       dbo.forums
   WHERE
       ForumID = @RootForumID

   UNION ALL

   -- select the child rows
   SELECT 
       f.ForumID, f.TopicCount, f.ReplyCount, f.LastPost
   FROM 
       dbo.forums f
   INNER JOIN
       CTE on f.ParentForumID = CTE.ForumID
)
SELECT 
    SUM(TopicCount) AS topics, 
    SUM(ReplyCount) AS replys,
    MAX(LastPost) AS 'Latest Post' 
FROM 
    CTE

当然，您可以将其包装到一个存储过程中，该过程将初始根"ForumID 作为参数.

Of course, you could wrap this into a stored procedure that would take the initial "root" ForumID as a parameter .

这篇关于带有 CTE 的递归查询 - 给定父级的子列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！