MySQL实现单查询中按设备类型动态展示维护任务结果

问题说明 #

现有基础查询可获取设备维护报告的核心信息，但需要根据设备类型关联对应维护任务，将任务名称作为动态列，展示工程师完成任务后的选项描述（无结果时显示NULL）。由于SQL静态语句无法直接实现动态列，需通过预处理动态SQL来完成。

实现方案 #

1. 关联所有表获取完整数据集

先将所有涉及的表关联，得到包含报告基础信息、任务名称、选项描述的原始数据集：

SELECT 
    DATE(r.created_timestamp) AS Date,
    loc.name AS Location,
    lg.name AS L_group,
    u.name AS Unit_name,
    u.id AS Unit_ID,
    CONCAT(u2.first_name, '           ', u2.last_name) AS Engineer,
    r.id AS Report_ID,
    rt.name AS Task_Name,
    rto.description AS Task_Result
FROM reports r
LEFT JOIN units u ON r.unit_id = u.id
LEFT JOIN users_2 u2 ON r.user_id = u2.id
LEFT JOIN locations loc ON u.location_id = loc.id
LEFT JOIN location_groups lg ON loc.locations_group_id = lg.id
-- 关联设备类型对应的任务
LEFT JOIN display_type_report_tasks dtrt ON u.unit_type_id = dtrt.unit_type_id
LEFT JOIN report_tasks rt ON dtrt.report_task_id = rt.id
-- 关联任务选中的选项
LEFT JOIN reports_tasks_selected_options rts ON r.id = rts.example_report_id
LEFT JOIN report_tasks_options rto ON rts.report_tasks_option_id = rto.id
WHERE r.created_timestamp >= '2022-01-01 00:00:00'
  AND loc.name NOT LIKE 'Company Vehicles'
  AND lg.name = 'Airports';

2. 动态生成透视查询语句

通过预处理语句自动生成任务列，实现动态透视效果：

-- 第一步：生成动态列的SQL片段
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Task_Name = ''',
      rt.name,
      ''' THEN Task_Result END) AS `',
      -- 自定义任务别名，比如把"Unit Cleaned-Internal"简化为"Clean-Int"
      REPLACE(rt.name, 'Unit Cleaned-', 'Clean-'),
      '`'
    )
  ) INTO @sql
FROM report_tasks rt
JOIN display_type_report_tasks dtrt ON rt.id = dtrt.report_task_id
JOIN units u ON dtrt.unit_type_id = u.unit_type_id
JOIN locations loc ON u.location_id = loc.id
JOIN location_groups lg ON loc.locations_group_id = lg.id
WHERE lg.name = 'Airports';

-- 第二步：拼接完整查询语句
SET @sql = CONCAT('
SELECT 
    DATE(r.created_timestamp) AS Date,
    loc.name AS Location,
    lg.name AS L_group,
    u.name AS Unit_name,
    u.id AS Unit_ID,
    CONCAT(u2.first_name, ''           '', u2.last_name) AS Engineer,
    r.id AS Report_ID,
    ', @sql, '
FROM reports r
LEFT JOIN units u ON r.unit_id = u.id
LEFT JOIN users_2 u2 ON r.user_id = u2.id
LEFT JOIN locations loc ON u.location_id = loc.id
LEFT JOIN location_groups lg ON loc.locations_group_id = lg.id
LEFT JOIN display_type_report_tasks dtrt ON u.unit_type_id = dtrt.unit_type_id
LEFT JOIN report_tasks rt ON dtrt.report_task_id = rt.id
LEFT JOIN reports_tasks_selected_options rts ON r.id = rts.example_report_id
LEFT JOIN report_tasks_options rto ON rts.report_tasks_option_id = rto.id
WHERE r.created_timestamp >= ''2022-01-01 00:00:00''
  AND loc.name NOT LIKE ''Company Vehicles''
  AND lg.name = ''Airports''
GROUP BY Date, Location, L_group, Unit_name, Unit_ID, Engineer, Report_ID;
');

-- 第三步：执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键说明 #

• 代码会自动抓取所有Airports组设备对应的任务名称，将其转为动态列，每个列展示该任务的完成结果。
• 若需要调整任务列的显示名称，可修改REPLACE(rt.name, 'Unit Cleaned-', 'Clean-')部分自定义别名规则。
• 注意：如果reports_tasks_selected_options表中关联报告的字段实际为report_id而非example_report_id，需自行替换对应字段名。

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