如何编写SQL语句实现单表行替换为另一表多行（不可修改原表）

嘿，这个需求其实核心是处理多值拆分+全量组合匹配+fallback取值，我来给你捋清楚怎么实现：

首先得拆解问题：我们需要先把Table1里用逗号打包的Code拆成单独行，然后生成所有Number和Table2 Code的组合，最后对每个组合匹配对应的值——有对应Code的记录就用它的Value，没有就用同一Number下rest对应的Value。

具体SQL语句（以PostgreSQL为例） #

WITH split_table1 AS (
    -- 拆分多值Code为单独行
    SELECT 
        Number,
        unnest(string_to_array(Code, ',')) AS split_code,
        Value
    FROM Table1
    WHERE Code <> 'rest'
    -- 单独保留rest的记录，作为兜底值来源
    UNION ALL
    SELECT 
        Number,
        'rest' AS split_code,
        Value
    FROM Table1
    WHERE Code = 'rest'
),
all_combinations AS (
    -- 生成所有Number和Table2 Code的全量组合
    SELECT DISTINCT 
        t1.Number,
        t2.Code
    FROM Table1 t1
    CROSS JOIN Table2 t2
)
-- 匹配取值，优先用具体Code的值，没有就用rest的兜底值
SELECT 
    ac.Number,
    ac.Code,
    COALESCE(st1.Value, rest.Value) AS Value
FROM all_combinations ac
LEFT JOIN split_table1 st1 
    ON ac.Number = st1.Number AND ac.Code = st1.split_code
LEFT JOIN split_table1 rest 
    ON ac.Number = rest.Number AND rest.split_code = 'rest'
ORDER BY ac.Number, ac.Code;

语句拆解说明 #

• split_table1 公共表表达式：把Table1里像A0,C2这种多值Code拆成单独的行，同时单独保留每个Number对应的rest记录，这样我们就有了所有具体Code和兜底的rest对应的Value。
• all_combinations 公共表表达式：通过CROSS JOIN把Table1里的Number（去重后）和Table2里的所有Code做全量组合，确保每个Number都能对应到Table2的每一个Code。
• 主查询：两次左连接分别匹配具体Code和rest的值，COALESCE函数会优先取具体Code对应的Value，如果找不到匹配（比如B1、D3这些），就自动用rest对应的500，完全符合你要的结果。

适配其他数据库（比如MySQL 8.0+） #

如果用的是MySQL，拆分逗号分隔字符串可以用JSON_TABLE，把split_table1替换成下面的写法就行，后面的逻辑完全一致：

WITH split_table1 AS (
    SELECT 
        t.Number,
        j.code AS split_code,
        t.Value
    FROM Table1 t
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t.Code, ',', '","'), '"]'),
        '$[*]' COLUMNS(code VARCHAR(10) PATH '$')
    ) j
    WHERE t.Code <> 'rest'
    UNION ALL
    SELECT 
        Number,
        'rest' AS split_code,
        Value
    FROM Table1
    WHERE Code = 'rest'
)

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