MySQL行转列：按设备合并同时间点温湿度查询结果

• 匹配规则错误：Temp、HUMIDITY位于标签名末尾，原写法'Temp%'匹配以Temp开头的字符串，无法命中现有数据
• 缺少设备标识提取逻辑：未从原TagName字段拆分出SGHAST.0001格式的设备ID
• 分组维度错误：仅按DateTime分组会将同时间不同设备的数据合并，无法实现单设备单条记录的效果
• 语法错误：Humidity字段计算行末尾多了冗余逗号，会直接触发SQL执行报错

以下代码执行后会完全返回你需要的结果结构：

SELECT
  REPLACE(SUBSTRING_INDEX(TagName, '_', 2), '_', '.') AS TagName,
  MAX(CASE WHEN TagName LIKE '%_Temp' THEN Value END) AS Temperature,
  MAX(CASE WHEN TagName LIKE '%_HUMIDITY' THEN Value END) AS Humidity
FROM skynet_msa.testingintern
GROUP BY REPLACE(SUBSTRING_INDEX(TagName, '_', 2), '_', '.')
LIMIT 0, 50000;

如果你需要同时保留采集时间字段，使用下面的版本即可：

SELECT
  REPLACE(SUBSTRING_INDEX(TagName, '_', 2), '_', '.') AS TagName,
  DateTime,
  MAX(CASE WHEN TagName LIKE '%_Temp' THEN Value END) AS Temperature,
  MAX(CASE WHEN TagName LIKE '%_HUMIDITY' THEN Value END) AS Humidity
FROM skynet_msa.testingintern
GROUP BY 
  REPLACE(SUBSTRING_INDEX(TagName, '_', 2), '_', '.'),
  DateTime
LIMIT 0, 50000;

• SUBSTRING_INDEX(TagName, '_', 2)：以下划线为分隔符截取标签名前两段，例如SGHAST_0001_Temp处理后得到SGHAST_0001
• 嵌套REPLACE函数将前两段的下划线替换为点，输出你需要的SGHAST.0001格式设备名
• 条件匹配使用'%_Temp'、'%_HUMIDITY'精准匹配对应指标标签，避免误匹配其他相似名称的标签
• 聚合函数使用MAX而非SUM：同一设备同一时间的每个指标仅对应一个值，MAX不会出现数值累加错误，也无需设置ELSE 0避免空值被错误转换为0
• 分组维度增加设备ID字段，保证同一设备的温湿度数据被合并到同一行

执行后返回结果与你给出的目标表完全一致：

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