如何根据非后缀带'-'的location对应的cp_id填充root_cp_id列

根据需求和示例数据，我们可以通过自连接+字符串截取+COALESCE函数实现root_cp_id的填充，核心逻辑如下：

• 提取每个location的根前缀：用SUBSTRING_INDEX(location, '-', 1)截取-之前的部分（无-则返回原location）；
• 优先匹配表中location等于该根前缀的行的cp_id；
• 若找不到匹配的location，则匹配cp_id等于该根前缀的行的cp_id；
• 兜底返回当前行的cp_id（避免无匹配的异常情况）。

示例数据表 #

假设表名为cp_table，原始数据如下：

实现SQL #

SELECT 
    t.cp_id,
    COALESCE(loc_root.cp_id, cp_root.cp_id, t.cp_id) AS root_cp_id,
    t.location
FROM cp_table t
-- 连接匹配根前缀location的行
LEFT JOIN cp_table loc_root 
    ON loc_root.location = SUBSTRING_INDEX(t.location, '-', 1)
-- 连接匹配根前缀cp_id的行
LEFT JOIN cp_table cp_root 
    ON cp_root.cp_id = SUBSTRING_INDEX(t.location, '-', 1)
ORDER BY t.cp_id;

执行结果 #

运行上述SQL后，将得到期望的结果：

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