如何实现MySQL标签表行转列 聚合生成指定结构汇总数据表

该需求完全可实现，无需修改原始存储的行式表结构，根据你的使用场景，可以选择数据库层聚合或者业务层重组两种实现路径。

方案1：MySQL层直接聚合（适合查询逻辑固定、无复杂交互的场景） #

核心逻辑是通过MySQL字符串函数拆分TagName字段的结构化信息，再通过分组聚合把行数据转成你需要的列格式：

• 从TagName中拆分三类核心信息：
  • 腔体标识：提取设备类型、设备编号，拼接为目标格式的Chamber字段
  • 指标类型：识别温度、湿度、电压、电流四类指标
  • 分区标识：识别Zone1、Zone1B这类分区信息，用于拼接多分区的电压、电流值
• 按Chamber分组，用聚合函数提取单值类指标（温湿度）、拼接多值类指标（电压、电流）

参考SQL如下：

SELECT
  -- 按实际业务规则调整Chamber拼接逻辑，示例为设备类型.编号格式
  CONCAT(device_type, '.', device_no) AS Chamber,
  MAX(CASE WHEN metric = 'Temp' THEN val END) AS Temperature,
  MAX(CASE WHEN metric = 'HUMIDITY' THEN val END) AS Humidity,
  GROUP_CONCAT(
    CASE WHEN metric = 'Voltage' THEN CONCAT(REPLACE(zone_flag, 'Zone', 'Zone '), ': ', val)
    ORDER BY zone_flag SEPARATOR ', '
  ) AS Voltage,
  GROUP_CONCAT(
    CASE WHEN metric = 'current' THEN CONCAT(REPLACE(zone_flag, 'Zone', 'Zone '), ': ', val)
    ORDER BY zone_flag SEPARATOR ', '
  ) AS Current
FROM (
  SELECT
    Value AS val,
    -- 提取设备编号、设备类型
    SUBSTRING_INDEX(SUBSTRING_INDEX(TagName, '.', 1), '_', -1) AS device_no,
    SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(TagName, '.', 1), '_', -2), '_', 1) AS device_type,
    -- 提取指标类型、分区标识
    SUBSTRING_INDEX(SUBSTRING_INDEX(TagName, '.', -1), '_', -1) AS metric,
    CASE
      WHEN SUBSTRING_INDEX(TagName, '.', -1) LIKE 'Zone%' 
      THEN SUBSTRING_INDEX(SUBSTRING_INDEX(TagName, '.', -1), '_', 1)
      ELSE NULL
    END AS zone_flag
  FROM your_raw_table
  -- 建议加时间范围过滤，大幅减少计算量
  -- WHERE DateTime BETWEEN '2022-06-01' AND '2022-06-04'
) t
-- 若同编号不同设备类型归属同一腔体，改为 GROUP BY device_no 即可
GROUP BY device_type, device_no;

注意：如果同个指标同个腔体存在多条不同时间的上报记录，需要再加一层子查询，先按TagName分组取DateTime最新的一条Value再做聚合，避免拼接历史过期数据。

方案2：PHP业务层重组（适合对接网页端、后续逻辑调整频繁的场景） #

由于你最终需要对接PHP+JavaScript的网页展示，该方案维护成本更低、灵活性更高：

• 用简单SQL查询原始数据，仅加必要的时间过滤条件，不做复杂聚合：

  SELECT TagName, DateTime, Value FROM your_raw_table WHERE DateTime >= '起始时间'
  

• PHP遍历查询结果集，按照和上述SQL一致的规则拆分TagName，组装为以Chamber为键的数组：遍历过程中把温湿度值、各分区的电压电流值分别存入对应Chamber的数组项中，同时可以按业务规则过滤过期历史值。
• 组装完成的结构化数据可以直接渲染到PHP模板，或者转成JSON格式返回给前端JavaScript，用于动态表格渲染、排序、筛选等交互。

• 原始表的DateTime字段务必加索引，时序类数据量增长快，加索引后按时间范围查询的性能会有明显提升。
• 若页面访问量较高，不要每次请求都实时查询聚合数据库，可以加一层缓存：比如每1~5分钟生成一次最新的聚合结果存在缓存中，前端请求直接读取缓存，降低数据库压力。
• 前端渲染表格时，电压、电流字段如果拼接内容较长，可以做省略+悬浮展开全文的交互，避免表格列过宽影响阅读。

内容的提问来源于stack exchange，提问作者Gracella Q Sumarlin