SQL场景求助：T_Schools与T_Children的ChildID新增/移除处理

场景1：新增ChildID时填充可用School行 #

你的原有UPDATE语句存在逻辑错误：M.ChildID <> N.ChildID的关联条件完全不符合需求——当M.ChildID为NULL时，该比较结果为NULL，无法正确匹配；且即使匹配，会让每个NULL行关联所有新增ChildID，导致更新混乱。

正确的做法是将新增的ChildID与可用School行按顺序一一匹配，需要用到窗口函数ROW_NUMBER()给两边的行编号，再通过编号关联更新。以下是适配你预期结果的SQL（假设SQL Server环境）：

-- 匹配新增ChildID与可用School行并更新
UPDATE target_school
SET target_school.ChildID = new_child.ChildID
FROM (
    -- 筛选出所有ChildID为NULL的可用School行，按SchoolID排序（对应你预期的顺序）
    SELECT 
        ID, 
        ChildID, 
        ROW_NUMBER() OVER (ORDER BY SchoolID) AS row_num
    FROM T_Schools
    WHERE ChildID IS NULL
) AS target_school
JOIN (
    -- 筛选出T_Children中未在T_Schools里的新增ChildID，按ChildID排序
    SELECT 
        ChildID, 
        ROW_NUMBER() OVER (ORDER BY ChildID) AS row_num
    FROM T_Children
    WHERE ChildID NOT IN (SELECT ChildID FROM T_Schools WHERE ChildID IS NOT NULL)
) AS new_child ON target_school.row_num = new_child.row_num

这段SQL会把Child7、Child8、Child9依次填入School7、School8、School9对应的行，完全符合你的预期结果。

场景2：移除ChildID时释放School行 #

当T_Children中移除某个ChildID时，只需将T_Schools中对应的ChildID设为NULL即可。如果是批量处理所有已移除的ChildID，用以下语句：

UPDATE T_Schools
SET ChildID = NULL
WHERE ChildID NOT IN (SELECT ChildID FROM T_Children)

如果是针对特定已移除的ChildID（比如Child3），可以直接指定条件：

UPDATE T_Schools
SET ChildID = NULL
WHERE ChildID = 'Child3'

内容的提问来源于stack exchange，提问作者Mufaddal