批量替换SQL查询中整数值为对应文本值的最优方案

针对你BASE表近20列需要替换1/2/3为No/Yes/N/A的需求，不想写多次JOIN或重复CASE，这里给你三种实用方案，优先推荐第一种通用高效的：

方案1：UNPIVOT + PIVOT 通用高效解 #

这个思路是先把多列转成键值对的行（UNPIVOT），只关联一次CODE表完成映射，再把行转回列（PIVOT），全程只碰一次CODE表，完美避免重复代码。

先明确基础表结构：

• BASE表：有主键id，还有col1到col20这20列存1/2/3
• CODE表：code_id对应映射值，比如1→'No'，2→'Yes'，3→'N/A'

SQL Server 示例代码： #

SELECT 
    id,
    [col1] AS col1,
    [col2] AS col2,
    -- 把col1到col20都列出来
    [col20] AS col20
FROM (
    -- 第一步：把多列拆成行，每列变成一条(id, 列名, 代码值)的记录
    SELECT 
        b.id,
        unpivoted.col_name,
        c.code_text
    FROM BASE b
    UNPIVOT (
        code_value FOR col_name IN (col1, col2, ..., col20)
    ) unpivoted
    -- 只关联一次CODE表，把所有代码值换成文本
    JOIN CODE c ON unpivoted.code_value = c.code_id
) src
-- 第二步：把映射后的行重新转成列
PIVOT (
    MAX(code_text) FOR col_name IN ([col1], [col2], ..., [col20])
) pivoted;

PostgreSQL 示例（用JSON转列替代UNPIVOT/PIVOT）： #

SELECT 
    id,
    (data->>'col1') AS col1,
    (data->>'col2') AS col2,
    -- 列出所有需要替换的列
    (data->>'col20') AS col20
FROM (
    SELECT 
        b.id,
        -- 把映射后的键值对重新拼成JSON对象
        jsonb_object_agg(u.col_name, c.code_text) AS data
    FROM BASE b
    -- 把BASE表转成键值对行，排除主键id
    CROSS JOIN LATERAL jsonb_each_text(to_jsonb(b) - 'id') AS u(col_name, code_value)
    -- 关联CODE表做映射
    JOIN CODE c ON u.code_value::int = c.code_id
    -- 只处理需要替换的列，避免碰其他无关列
    WHERE u.col_name IN ('col1', 'col2', ..., 'col20')
    GROUP BY b.id
) src;

方案2：自定义标量函数（简洁但注意性能） #

如果你的数据库支持自定义函数，可以把CODE表的映射逻辑封装成一个函数，查询时直接调用，代码写起来最省事。

MySQL 示例： #

-- 先创建映射函数
DELIMITER //
CREATE FUNCTION get_code_text(code_id INT) 
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE text_val VARCHAR(10);
    SELECT code_text INTO text_val FROM CODE WHERE code_id = get_code_text.code_id;
    RETURN text_val;
END //
DELIMITER ;

-- 查询时直接调用函数
SELECT 
    id,
    get_code_text(col1) AS col1,
    get_code_text(col2) AS col2,
    -- ... 其他列
    get_code_text(col20) AS col20
FROM BASE;

⚠️ 注意：这个方法在数据量很大的时候可能拖慢性能，因为每列每行都会单独调用一次函数。如果你的BASE表数据量不大，这个方法最省心。

方案3：固定映射字典（适合映射不变的场景） #

如果CODE表的映射是固定死的（1始终是No，2始终是Yes），可以用WITH子句生成一个临时映射表，再关联到每一列。不过本质还是多次JOIN，列多的话代码会很长，适合列数少的情况，20列的话不如方案1。

WITH code_map AS (
    SELECT 1 AS code_id, 'No' AS code_text UNION ALL
    SELECT 2, 'Yes' UNION ALL
    SELECT 3, 'N/A'
)
SELECT 
    b.id,
    cm1.code_text AS col1,
    cm2.code_text AS col2,
    -- ... 依次给每个列关联映射
    cm20.code_text AS col20
FROM BASE b
JOIN code_map cm1 ON b.col1 = cm1.code_id
JOIN code_map cm2 ON b.col2 = cm2.code_id
-- ... 重复到col20
JOIN code_map cm20 ON b.col20 = cm20.code_id;

方案对比 #

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