如何更新孩子的数量

Question

我有一个包含以下字段的表：

ID
IDParent
NumberOfChilds

它是一个表，其中包含一个字段，用于了解一行具有的子项数。I NumberOfChilds 是一个新字段，所以我需要用正确的值更新它。我正在尝试这样的查询：

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

但是此查询将 0 设置为所有行，无论行是否有子项。如何使用正确的值设置新字段？

Answer 1

问题出在子查询中。正确的查询应为：

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

Answer 2

您没有将最外层的表MyTable与相关子查询相关联(现在根本不相关(，因此没有一行的ParentID等于它自己的ID，并且对所有行都返回 0。

您可以使用简单的 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

我强烈建议不要在计算时存储此值，并且在每次插入、更新或删除任何记录时都需要刷新。您应该按需计算(通过视图或在需要时使用它(，而不是将其存储起来。

Answer 3

您可以使用

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 IDParet 时，它被视为Parent 。因此，只需过滤掉它并进行更新操作即可。