SQL主表关联多语言子表动态行转列实现方案问询

你需要的是**动态行转列（动态透视）**能力，普通静态SQL执行前必须确定返回列结构，无法适配动态新增的语言类型，因此必须通过动态拼接SQL的方式实现，以下是主流数据库的实现方案：

MySQL 实现 #

步骤1：拼接动态列查询片段 #

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
           CONCAT('MAX(IF(l.lang_code = ''', lang_code, ''', l.title, NULL)) AS `title-', lang_code, '`')
       ) INTO @sql
FROM table_1_lang;

步骤2：拼接完整查询语句 #

SET @sql = CONCAT('SELECT t.uuid, t.code, ', @sql, ' 
                   FROM table_1 t
                   LEFT JOIN table_1_lang l ON t.uuid = l.uuid
                   GROUP BY t.uuid, t.code');

步骤3：执行动态SQL #

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 实现 #

通过PL/pgSQL存储函数实现动态返回：

CREATE OR REPLACE FUNCTION get_multilang_table_1()
RETURNS SETOF RECORD AS $$
DECLARE
    cols text;
    query_str text;
BEGIN
    -- 拼接动态列
    SELECT string_agg(DISTINCT format('MAX(CASE WHEN l.lang_code = %L THEN l.title END) AS %I', lang_code, 'title-' || lang_code), ', ')
    INTO cols
    FROM table_1_lang;

    -- 拼接完整查询
    query_str := format('SELECT t.uuid, t.code, %s FROM table_1 t LEFT JOIN table_1_lang l ON t.uuid = l.uuid GROUP BY t.uuid, t.code', cols);

    -- 返回结果
    RETURN QUERY EXECUTE query_str;
END;
$$ LANGUAGE plpgsql;

调用方式（需要提前指定返回列结构，或者在业务侧先查询语言列表再拼接调用语句）：

SELECT * FROM get_multilang_table_1() AS t(uuid text, code text, "title-en" text, "title-de" text);

注意事项 #

• 若lang_code为用户可控参数，需要做好参数校验避免SQL注入风险
• 若对接的BI工具、ORM框架不支持动态返回列结构，可以先查询所有已配置的语言列表，在业务代码层拼接静态SQL语句执行查询

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