如何在SQL中遍历层级表填充另一张表的对应列？

问题背景 #

现有三张表：

• Table One（待填充表）：

• Level 1 Hierarchy（一级层级表）：

• Level 2 Hierarchy（二级层级表）：

需要将Table One填充为目标格式，避免手动编写大量LIKE语句的低效操作。

解决方案 #

核心思路是利用字符串前缀匹配，通过层级表的Code前缀与Table One的Name前缀自动关联，实现批量匹配填充。以下是适配主流数据库的通用写法：

1. 查询填充（不修改原表） #

如果只需查看填充结果，可使用SELECT语句：

SELECT
    t.Name,
    l1.Code AS `Code 1`,
    l1.Description AS `Description 1`,
    l2.Code AS `Code 2`,
    l2.Description AS `Description 2`
FROM `Table One` t
-- 匹配一级层级：取Name首字母与Level 1 Code首字母一致
LEFT JOIN `Level 1 Hierarchy` l1 
    ON LEFT(t.Name, 1) = LEFT(l1.Code, 1)
-- 匹配二级层级：取Name前两个字母与Level 2 Code前两个字母一致
LEFT JOIN `Level 2 Hierarchy` l2 
    ON LEFT(t.Name, 2) = LEFT(l2.Code, 2);

2. 更新原表（直接写入数据） #

如果需要直接修改Table One的空字段，可使用UPDATE语句（执行前建议备份数据）：

MySQL版本

UPDATE `Table One` t
JOIN `Level 1 Hierarchy` l1 
    ON LEFT(t.Name, 1) = LEFT(l1.Code, 1)
JOIN `Level 2 Hierarchy` l2 
    ON LEFT(t.Name, 2) = LEFT(l2.Code, 2)
SET
    t.`Code 1` = l1.Code,
    t.`Description 1` = l1.Description,
    t.`Code 2` = l2.Code,
    t.`Description 2` = l2.Description;

SQL Server版本

UPDATE t
SET
    t.`Code 1` = l1.Code,
    t.`Description 1` = l1.Description,
    t.`Code 2` = l2.Code,
    t.`Description 2` = l2.Description
FROM `Table One` t
INNER JOIN `Level 1 Hierarchy` l1 
    ON LEFT(t.Name, 1) = LEFT(l1.Code, 1)
INNER JOIN `Level 2 Hierarchy` l2 
    ON LEFT(t.Name, 2) = LEFT(l2.Code, 2);

补充说明 #

• 该方案依赖层级Code的前缀规则（一级Code首字母对应Name首字母，二级Code前两位对应Name前两位），若编码规则有调整，只需修改LEFT()函数的截取长度即可适配。
• 如果存在前缀冲突（如两个Name前两位相同但对应不同二级Code），需补充额外关联规则（比如新增匹配字段），但当前示例的规则可直接适用。

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