如何更新孩子的数量?

问题出在子查询中。 正确的查询应该是:

Update MyTable 
set NumberOfChilds = (select count(t.ID) 
from MyTable as t 
where t.IDParent = MyTable.ID)

你没有把最外面的桌子联系起来 MyTable ParentID 等于它自己的 ID

您可以通过简单的CTE或子查询解决此问题:

;WITH Counts AS
(
    SELECT
        M.ID,
        AmountOfChildren = COUNT(1)
    FROM
        MyTable AS M
        INNER JOIN MyTable AS C ON M.IDParent = M.ID
    GROUP BY
        M.ID
)
Update M set 
    NumberOfChilds = ISNULL(C.AmountOfChildren, 0)
FROM
    MyTable AS M
    LEFT JOIN Counts AS C ON M.ID = C.ID

但是,如果您想要遍历关系并计算子关系的所有子关系,那么这会变得更复杂一些,因为您需要递归遍历所有子关系,以了解它向下移动了多少层。

设置:

DECLARE @MyTable TABLE (
    ID INT,
    IDParent INT,
    NumberOfChilds INT)

INSERT INTO @MyTable (ID, IDParent)
VALUES 
    (1, NULL),
    (2, NULL),

    (3, 1),
    (4, 1),
    (5, 2),

    (6, 4),
    (7, 4),
    (8, 7),
    (9, 7),
    (10, 9)

更新:

;WITH RecursiveCTE AS
(
    -- Anchor
    SELECT
        StartingID = M.ID,
        CurrentID = M.ID,
        Level = 0
    FROM
        @MyTable AS M

    UNION ALL

    -- Recursion
    SELECT
        StartingID = R.StartingID,
        CurrentID = M.ID,
        Level = R.Level + 1
    FROM
        RecursiveCTE AS R
        INNER JOIN @MyTable AS M ON M.IDParent = R.CurrentID
),
MaxLevelByID AS
(
    SELECT
        R.StartingID,
        NumberOfChilds = COUNT(DISTINCT(R.CurrentID)) - 1 -- Don't count self
    FROM
        RecursiveCTE AS R
    GROUP BY
        R.StartingID
)
UPDATE L SET
    NumberOfChilds = M.NumberOfChilds
FROM
    @MyTable AS L
    INNER JOIN MaxLevelByID AS M ON L.ID = M.StartingID

结果:

ID  IDParent    NumberOfChilds
1   NULL        7
2   NULL        1
3   1           0
4   1           5
5   2           0
6   4           0
7   4           3
8   7           0
9   7           1
10  9           0

不 要在计算时存储此值,每次插入、更新或删除时都需要刷新 任何

你可以用 APPLY

UPDATE mt
     SET mt.NumberOfChilds = mt1.NumberOfChilds 
FROM MyTable mt CROSS APPLY
     (SELECT COUNT(*) AS NumberOfChilds 
      FROM MyTable mt1
      WHERE mt1.IDParent = mt.id
     ) mt1
WHERE mt.IDParent IS NULL;

ID 有 null 那么它不被认为是 Parent