Counting a root's children in SQL

Let's say I have a table with 3 columns:

1. item ID 'ID'
2. parent ID 'ParentID'
3. item name 'Title'

开发者_运维知识库

Now, how should I count how many children a Root has?

---

SELECT COUNT(*)
FROM T
WHERE ParentID = @ParentID

If you want descendants not just immediate children you would need a recursive CTE.

;WITH R AS
(
SELECT ID
FROM T
WHERE ParentID = @RootID
UNION ALL
SELECT T.ID
FROM T
JOIN R ON R.ID = T.ParentID
)
SELECT COUNT(*)
FROM R