SQL如何合并存在缺失的同Cd、MeasNr分组行并按StNr加字段前缀

最优方案：条件聚合（行转列） #

这种方案兼容性最强，写法简洁，不需要处理多表JOIN的关联逻辑，天然可以解决站点缺失的问题：

SELECT
  Cd,
  MeasNr,
  MAX(CASE WHEN StNr = 1 THEN Var1 ELSE NULL END) AS st1_Var1,
  MAX(CASE WHEN StNr = 1 THEN Var2 ELSE NULL END) AS st1_Var2,
  MAX(CASE WHEN StNr = 1 THEN Var3 ELSE NULL END) AS st1_Var3,
  MAX(CASE WHEN StNr = 2 THEN Var1 ELSE NULL END) AS st2_Var1,
  MAX(CASE WHEN StNr = 2 THEN Var2 ELSE NULL END) AS st2_Var2,
  MAX(CASE WHEN StNr = 2 THEN Var3 ELSE NULL END) AS st2_Var3,
  MAX(CASE WHEN StNr = 3 THEN Var1 ELSE NULL END) AS st3_Var1,
  MAX(CASE WHEN StNr = 3 THEN Var2 ELSE NULL END) AS st3_Var2,
  MAX(CASE WHEN StNr = 3 THEN Var3 ELSE NULL END) AS st3_Var3
FROM Measurements
GROUP BY Cd, MeasNr
ORDER BY Cd, MeasNr

逻辑说明 #

同一个Cd + MeasNr分组下，每个StNr只会对应一行数据，用MAX/MIN聚合可以跳过NULL值，精准取出对应站点的变量值，哪怕某个站点完全不存在该分组的记录，也会自动填充NULL，不会漏掉分组。 #

原FULL JOIN写法的问题修复 #

你之前的FULL JOIN没有得到正确结果，是因为第二次关联st3的时候，只判断了和st1的匹配关系，当分组不存在StNr=1的记录时，st2的记录无法和st3的同分组记录关联，就会拆分为两行。修正关联条件即可：

SELECT 
  COALESCE(st1.DtTm, st2.DtTm, st3.DtTm) AS DtTime,
  COALESCE(st1.Cd, st2.Cd, st3.Cd) AS Cd,
  COALESCE(st1.MeasNr, st2.MeasNr, st3.MeasNr) AS MeasNr,
  st1.Var1 AS st1_Var1,
  st1.Var2 AS st1_Var2,
  st1.Var3 AS st1_Var3,
  st2.Var1 AS st2_Var1,
  st2.Var2 AS st2_Var2,
  st2.Var3 AS st2_Var3,
  st3.Var1 AS st3_Var1,
  st3.Var2 AS st3_Var2,
  st3.Var3 AS st3_Var3
FROM (SELECT * FROM Measurements WHERE StNr = 1) AS st1
FULL JOIN (SELECT * FROM Measurements WHERE StNr = 2) AS st2 
  ON st1.Cd = st2.Cd AND st1.MeasNr = st2.MeasNr
FULL JOIN (SELECT * FROM Measurements WHERE StNr = 3) AS st3 
  ON COALESCE(st1.Cd, st2.Cd) = st3.Cd AND COALESCE(st1.MeasNr, st2.MeasNr) = st3.MeasNr
ORDER BY Cd, MeasNr

这里用COALESCE代替了冗余的CASE判断，同时修正了st3的关联条件，优先取st1或st2的分组字段和st3匹配，就能保证同组数据合并到同一行。

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