如何扩展SQL查询将子表table_2数据转为动态列合并到主表结果中

方案1：已知order最大取值（推荐新手使用） #

如果已经明确table_2中order字段的最大取值（比如示例中最大为2），直接用条件聚合写法即可，兼容MySQL、PostgreSQL、SQL Server等绝大多数主流SQL方言：

SELECT
  t1.uuid,
  t1.code,
  -- 原有多语言转置逻辑
  MAX(CASE WHEN t1l.lang_code = 'en' THEN t1l.title END) AS `title-en`,
  MAX(CASE WHEN t1l.lang_code = 'de' THEN t1l.title END) AS `title-de`,
  -- 新增动态condition列，每一个order值对应2列
  MAX(CASE WHEN t2.order = 0 THEN t2.list_code END) AS `condition-0-list_code`,
  MAX(CASE WHEN t2.order = 0 THEN t2.value END) AS `condition-0-value`,
  MAX(CASE WHEN t2.order = 1 THEN t2.list_code END) AS `condition-1-list_code`,
  MAX(CASE WHEN t2.order = 1 THEN t2.value END) AS `condition-1-value`,
  MAX(CASE WHEN t2.order = 2 THEN t2.list_code END) AS `condition-2-list_code`,
  MAX(CASE WHEN t2.order = 2 THEN t2.value END) AS `condition-2-value`
  -- 如果还有更大的order值，按上面的格式继续加对应行即可
FROM table_1 t1
LEFT JOIN table_1_lang t1l ON t1.uuid = t1l.uuid
LEFT JOIN table_2 t2 ON t1.uuid = t2.uuid
GROUP BY t1.uuid, t1.code

逻辑说明：CASE语句会匹配对应order值的记录，无匹配时返回空，MAX聚合函数会过滤掉空值，取到唯一匹配的记录值，无对应记录的列最终返回空值，完全符合需求。

方案2：order最大值不固定（动态SQL实现） #

如果order的最大值会随数据变化，需要用动态SQL自动生成查询语句，以下是MySQL的实现示例：

-- 先查询最大的order值，生成需要拼接的SQL片段
SET @sql = NULL;
SELECT GROUP_CONCAT(
  'MAX(CASE WHEN t2.order = ', order_num, ' THEN t2.list_code END) AS `condition-', order_num, '-list_code`,',
  'MAX(CASE WHEN t2.order = ', order_num, ' THEN t2.value END) AS `condition-', order_num, '-value`'
) INTO @sql
FROM (SELECT DISTINCT `order` AS order_num FROM table_2 ORDER BY order_num) t;

-- 拼接完整SQL语句
SET @sql = CONCAT('SELECT t1.uuid, t1.code, MAX(CASE WHEN t1l.lang_code = ''en'' THEN t1l.title END) AS `title-en`, MAX(CASE WHEN t1l.lang_code = ''de'' THEN t1l.title END) AS `title-de`, ',
                  @sql,
                  ' FROM table_1 t1 LEFT JOIN table_1_lang t1l ON t1.uuid = t1l.uuid LEFT JOIN table_2 t2 ON t1.uuid = t2.uuid GROUP BY t1.uuid, t1.code');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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