;WITH CTE AS
(
SELECT forumId,ParentId,HitNumber,
Seq = CAST(','+CAST(forumId AS VARCHAR(2)) AS VARCHAR(20))
FROM #tab1
WHERE ParentId IS NULL
UNION ALL
SELECT DD2.forumId, DD2.ParentId, DD2.HitNumber,
Seq = CAST(CTE.Seq + ',' + CAST(DD2.forumId AS VARCHAR(2)) AS VARCHAR(20))
FROM #tab1 AS DD2
INNER JOIN CTE
ON CTE.ForumId = DD2.parentId
)
SELECT c.forumId, c.ParentId,
x.TotalHitNumber
FROM CTE c
CROSS APPLY (
SELECT TotalHitNumber = SUM(HitNumber)
FROM CTE
WHERE Seq+',' LIKE '%,'+CAST(c.forumId AS VARCHAR(2))+',%'
) x
ORDER BY c.forumId
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden