SQL表连接：根据指定字段值将单列数据拆分映射为两个结果列

基础信息 #

• 涉及表：INFO表、DEVICE表
  • INFO表字段：isite（站点ID）、inum（设备序号）、idet（数据类型ID）、idata（数据值）。站点332下，idet=20对应的值为设备MAC地址，idet=22对应的值为设备厂商名称
  • DEVICE表字段：dsite（站点ID）、dnum（设备序号）、dname（设备名称）。站点332下序号1/2/3对应的设备名分别为abc123、def456、ghi789
• 关联规则：通过站点ID、设备序号匹配两表数据
• 原有问题：初始SQL直接关联后，同一设备会返回两行，分别对应MAC、厂商两类数据，无法在同一行展示所有信息。初始SQL如下：

SELECT d.dname as Unit_name, i.idata as Unit_Data FROM DEVICE d 
LEFT JOIN INFO i 
    ON d.dsite = i.isite AND d.dnum = i.inum 
WHERE (i.idet = 20 OR i.idet = 22)
AND d.dsite = 332;

• 预期输出：每个设备占一行，列分别为设备名、MAC地址、厂商名称。

正确SQL写法 #

通过条件聚合实现行转列，兼容性强、执行效率高：

SELECT 
    d.dname AS Unit_name,
    MAX(CASE WHEN i.idet = 20 THEN i.idata END) AS Unit_MAC,
    MAX(CASE WHEN i.idet = 22 THEN i.idata END) AS Unit_Manufacturer
FROM DEVICE d 
LEFT JOIN INFO i 
    ON d.dsite = i.isite 
    AND d.dnum = i.inum
    AND i.idet IN (20, 22)
WHERE d.dsite = 332
GROUP BY d.dsite, d.dnum, d.dname;

写法说明 #

• 将idet的过滤条件放在LEFT JOIN关联子句中，避免左连接被强制转换为内连接，保证缺失MAC或厂商数据的设备也能正常出现在结果中
• 用CASE WHEN按idet取值分别提取对应的数据值，配合MAX()聚合函数、按设备维度分组，即可将两类数据拆分到独立列，实现单设备单行展示。正常业务场景下同一设备同一数据类型只会对应一条有效值，MAX()不会改变实际取值
• 该写法适配MySQL、PostgreSQL、SQL Server等主流数据库，不需要额外的特殊语法支持。

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