MySQL子查询JOIN更新tradebook失败，目标字段仍为NULL

你的更新语句存在两个核心问题：

1. 关联条件缺失：直接JOIN volatility表时仅匹配了Ticker，没有关联Vol_ref和对应的最大时间，导致每个tradebook行会匹配该Ticker下所有volatility的记录。当执行CASE判断时，只有某一行刚好对应某个Vol_ref的最新时间才会赋值，其余行的CASE返回NULL，而MySQL在多匹配行的更新场景中，最终会用最后匹配行的结果覆盖，大概率最后一行不满足任何CASE条件，导致目标字段为NULL。
2. CASE逻辑的局限性：当前CASE仅在单条volatility记录时间等于某类Vol_ref的最大时间时赋值，但一条volatility记录只能对应一个Vol_ref，所以同一行最多只有一个CASE分支生效，其余字段会被设置为NULL，进一步加剧了字段为空的问题。

先通过子查询获取每个Ticker+Vol_ref组合的最新波动率，再将结果转成宽表结构，最后和tradebook关联更新，确保每个字段都能匹配到对应Vol_ref的最新值：

UPDATE `tradebook` AS atb
JOIN (
    -- 先获取每个(Ticker, Vol_ref)的最新Volatility
    SELECT 
        bv.Ticker,
        MAX(CASE WHEN bv.Vol_ref = '10m-30m' THEN bv.Volatility END) AS `10m_30m_vol`,
        MAX(CASE WHEN bv.Vol_ref = '5m-30m' THEN bv.Volatility END) AS `5m_30m_vol`,
        MAX(CASE WHEN bv.Vol_ref = '5m-1h' THEN bv.Volatility END) AS `5m_1h_vol`,
        MAX(CASE WHEN bv.Vol_ref = '10m-1h' THEN bv.Volatility END) AS `10m_1h_vol`
    FROM `volatility` AS bv
    JOIN (
        -- 获取每个(Ticker, Vol_ref)的最新时间
        SELECT 
            Ticker, 
            Vol_ref, 
            MAX(Database_time_milsec) AS latest_time
        FROM `volatility`
        GROUP BY Ticker, Vol_ref
    ) AS latest_times 
        ON bv.Ticker = latest_times.Ticker 
        AND bv.Vol_ref = latest_times.Vol_ref 
        AND bv.Database_time_milsec = latest_times.latest_time
    GROUP BY bv.Ticker
) AS bv_latest_vols ON atb.ticker = bv_latest_vols.Ticker
SET 
    atb.`10m_1h_vol` = bv_latest_vols.`10m_1h_vol`,
    atb.`10m_30m_vol` = bv_latest_vols.`10m_30m_vol`,
    atb.`5m_1h_vol` = bv_latest_vols.`5m_1h_vol`,
    atb.`5m_30m_vol` = bv_latest_vols.`5m_30m_vol`;

1. 先单独执行子查询bv_latest_vols，确认每个Ticker对应的四个波动率字段是否正确获取到最新值。
2. 再执行完整的UPDATE语句，检查tradebook表的目标字段是否更新成功。

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