MySQL UPDATE中SELECT赋值为NULL问题及树形结构更新修复

嘿，我来帮你捋捋这个问题——你的UPDATE语句执行后没变化，根本不是什么单行单列没法赋值的问题，大概率是这几个细节没考虑到：

问题原因分析 #

1. WHERE条件把目标节点排除了 #

你原语句末尾的WHERE ProcessID > 0 and MainStructID = 0 and SubStructID = 0是硬限制：只有当前MainStructID和SubStructID都是0的节点才会被更新。如果ProcessID=24的行原本这两个字段就不是0，那这条语句根本不会碰它。

你可以先跑这条查询确认下：

SELECT ProcessID, ParentID, MainStructID, SubStructID 
FROM itmanagement.PRC_processes 
WHERE ProcessID = 24;

2. 树形结构更新顺序搞反了 #

你的树深度是5，叶子节点在最底层（深度5），父节点层级是4→3→2→1。父节点的统计依赖子节点的最终值，但如果你一次性执行UPDATE，上层节点（比如深度3）的子节点（深度4）可能还没被更新，导致统计不到有效数据；或者如果你的WHERE条件只允许更新初始为0的节点，那第一次更新完深度4的节点后，它们的Main/SubID不再是0，下次才能更新上层节点，但你只执行了一次，自然没效果。

3. 子查询冗余（非核心问题，但影响效率） #

你在CASE里重复写了两次完全一样的子查询，不仅浪费数据库资源，还容易出错，但这不是数据没变化的直接原因。

修复方法 #

第一步：先确认目标节点的初始状态 #

先跑上面的查询，看看ProcessID=24的节点是否满足UPDATE的WHERE条件。如果它的Main/SubID不是0，要么去掉WHERE里的MainStructID = 0 and SubStructID = 0，要么根据你的业务需求调整条件（比如允许覆盖已有值）。

第二步：按层级从下往上更新 #

必须从最底层的父节点（直接连叶子的深度4节点）开始，逐层往上更新到根节点。如果你的表有Depth字段记录层级，那就方便了：

1. 先更新深度4的节点
2. 再更新深度3的节点
3. 以此类推直到根节点

如果没有层级字段，可以用递归CTE先计算每个节点的深度：

WITH RecursiveProcesses AS (
    SELECT 
        ProcessID,
        ParentID,
        MainStructID,
        SubStructID,
        1 AS Depth -- 假设根节点深度是1
    FROM itmanagement.PRC_processes
    WHERE ParentID IS NULL OR ParentID = 0 -- 根节点条件，根据你的表结构调整
    UNION ALL
    SELECT 
        p.ProcessID,
        p.ParentID,
        p.MainStructID,
        p.SubStructID,
        rp.Depth + 1 AS Depth
    FROM itmanagement.PRC_processes p
    JOIN RecursiveProcesses rp ON p.ParentID = rp.ProcessID
)
SELECT * FROM RecursiveProcesses;

第三步：优化UPDATE语句，用预统计替代重复子查询 #

用CTE先统计每个父节点最频繁的Main/SubStructID，再关联更新，效率更高也更清晰：

WITH ChildStats AS (
    SELECT 
        ParentID,
        -- 取出现次数最多的MainStructID
        FIRST_VALUE(MainStructID) OVER (PARTITION BY ParentID ORDER BY COUNT(*) DESC) AS MostFreqMainID,
        -- 取出现次数最多的SubStructID
        FIRST_VALUE(SubStructID) OVER (PARTITION BY ParentID ORDER BY COUNT(*) DESC) AS MostFreqSubID
    FROM itmanagement.PRC_processes
    GROUP BY ParentID, MainStructID, SubStructID
)
UPDATE itmanagement.PRC_processes parent
SET 
    MainStructID = COALESCE(cs.MostFreqMainID, 0),
    SubStructID = COALESCE(cs.MostFreqSubID, 0)
FROM ChildStats cs
WHERE parent.ProcessID = cs.ParentID
-- 这里添加层级筛选，比如先更新深度4的节点
AND parent.Depth = 4; -- 如果有Depth字段的话

如果你的数据库不支持CTE（比如MySQL 5.7及以前），可以用子查询关联的方式：

UPDATE itmanagement.PRC_processes parent
JOIN (
    SELECT 
        ParentID,
        MAX(CASE WHEN rn_main = 1 THEN MainStructID END) AS MostFreqMainID,
        MAX(CASE WHEN rn_sub = 1 THEN SubStructID END) AS MostFreqSubID
    FROM (
        SELECT 
            ParentID,
            MainStructID,
            SubStructID,
            ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY COUNT(*) DESC) AS rn_main,
            ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY COUNT(*) DESC) AS rn_sub
        FROM itmanagement.PRC_processes
        GROUP BY ParentID, MainStructID, SubStructID
    ) sub
    WHERE rn_main = 1 OR rn_sub = 1
    GROUP BY ParentID
) cs ON parent.ProcessID = cs.ParentID
SET 
    MainStructID = COALESCE(cs.MostFreqMainID, 0),
    SubStructID = COALESCE(cs.MostFreqSubID, 0)
-- 添加层级筛选
-- AND parent.Depth = 4;

第四步：逐层验证更新结果 #

每次更新完一层后，跑这条查询确认：

SELECT ProcessID, MainStructID, SubStructID 
FROM itmanagement.PRC_processes 
WHERE ProcessID = 24;

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