如何编写SQL实现结果表列数随State表行数自适应调整

实现逻辑说明 #

静态SQL无法支持动态列输出，需通过动态SQL拼接+行转列的方式实现，以下为MySQL环境的可运行示例，其他数据库可对应调整动态语法即可，原有业务逻辑全部保留：

-- 步骤1：根据当前State表的工位ID，动态拼接每一个工位对应的区域、时间列SQL片段
SET @dynamic_col_sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      -- 生成工位对应区域列
      'MAX(CASE WHEN s.state_id = ''', state_id, ''' THEN a.area_name END) AS State', state_id, '_Area,',
      -- 生成工位对应维修时间列，保留原有时区转换规则
      'MAX(CASE WHEN s.state_id = ''', state_id, ''' THEN CONVERT_TZ(r.repair_start_time, "+00:00", si.factory_timezone) END) AS State', state_id, '_RepairTime'
    )
  ) INTO @dynamic_col_sql
FROM State s;

-- 步骤2：拼接完整查询SQL，保留原有五张表的关联逻辑、字段映射规则
SET @full_query_sql = CONCAT('
SELECT 
  c.car_id,
  c.car_license,
  c.car_type,
  ', @dynamic_col_sql, '
FROM Cars c
LEFT JOIN Repair r ON c.car_id = r.car_id
LEFT JOIN State s ON r.state_id = s.state_id
LEFT JOIN StateInfo si ON s.state_id = si.state_id
LEFT JOIN Area a ON s.area_id = a.area_id
GROUP BY c.car_id, c.car_license, c.car_type
');

-- 步骤3：执行动态生成的SQL，得到列数自适应的结果表
PREPARE exec_stmt FROM @full_query_sql;
EXECUTE exec_stmt;
DEALLOCATE PREPARE exec_stmt;

适配说明 #

• 执行时会自动读取当前汽修厂State表的所有工位ID生成对应列，无需修改硬编码的state_id，适配所有门店的工位数量差异
• 原有逻辑的表关联关系、时区转换规则、字段映射要求全部保留，仅通过动态拼接替换了硬编码的固定列部分
• PostgreSQL环境可使用crosstab函数或format方法实现动态拼接，SQL Server环境可使用PIVOT+变量拼接实现，核心逻辑和上述方案一致

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