代码之家 › 专栏 › 技术社区 › Álvaro García

如何更新孩子的数量?

tsql sql-server

Álvaro García · 技术社区 · 5 年前

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

ID
IDParent
NumberOfChilds

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

但是这个查询将0设置为所有行,不管一行是否有child。如何将新字段设置为正确的值?

3 回复 | 直到 5 年前

Michail Papadakis 5 年前

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

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

EzLo tumao kaixin 5 年前

你没有把最外面的桌子联系起来 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

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

Yogesh Sharma 5 年前

你可以用 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