MySQL查询：将Clust为0的记录填充为上方最近非0Clust对应ID

我有一组按ID分类学排序的鸟类物种名称数据，其中Clust值为0的是亚种，需将这些亚种的Clust值填充为表格中其上方最近的Clust≠0的物种对应的ID值。

我尝试了以下左连接查询：

select b.id, b.clust, b.code, (select max(a.id) where a.clust<>0 and a.id<b.id)   
    from db as a 
    left join db as b on a.id=b.id;

但无论是否使用max函数，返回的new_clust列全是Null值，推测是强制a.id=b.id导致的。希望用MySQL查询而非面向过程语言解决该问题。

原查询问题分析 #

你的左连接条件a.id=b.id让表a和表b的每一行都只能匹配自身，子查询里的a.id<b.id永远不成立，所以子查询自然返回Null，这就是问题根源。

MySQL 8.0+ 版本（窗口函数方案） #

利用LAST_VALUE窗口函数，按ID排序后，取当前行及之前所有行中最后一个Clust≠0的ID：

SELECT 
    id,
    clust,
    code,
    LAST_VALUE(CASE WHEN clust <> 0 THEN id END) OVER (
        ORDER BY id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS new_clust
FROM db;

这个方案依赖MySQL 8.0及以上版本支持的窗口函数，逻辑清晰且性能稳定。

MySQL 5.x 版本（用户变量方案） #

如果使用的是MySQL 5.x这类不支持窗口函数的版本，可以用用户变量追踪最近的非0Clust对应的ID：

SELECT 
    id,
    clust,
    code,
    @last_non_zero_id := CASE 
        WHEN clust <> 0 THEN id 
        ELSE @last_non_zero_id 
    END AS new_clust
FROM db, (SELECT @last_non_zero_id := NULL) AS init_var
ORDER BY id;

这里通过初始化变量@last_non_zero_id，遍历每一行时：

• 若当前行Clust≠0，更新变量为当前ID
• 若当前行是亚种（Clust=0），则沿用变量中存储的上一个非0Clust的ID

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