时间戳

首页 > 解决方案 > 如何加入并非全部可用的行？

sql - 如何加入并非全部可用的行？

问题描述

当可能发生一两行丢失时，如何在 SQL 中加入一些有序的（按 StNr）行？我有下表。我们称之为测量：

id  DtTm         Cd    MeasNr  StNr  Var1  Var2  Var3
-----------------------------------------------------
1   2021-10-25   abc   1       2     1.5   3.7   4.6
2   2021-10-25   abc   1       3     NULL  2.6   2.8
3   2021-10-25   def   3       1     3.5   NULL  3.6
4   2021-10-25   abc   2       1     2.6   NULL  1.5
5   2021-10-25   def   3       3     NULL  2.8   3.1
6   2021-10-25   abc   2       2     3.6   2.5   2.7
7   2021-10-25   xyz   1       2     2.2   3.0   2.6
8   2021-10-25   xyz   1       3     1.8   2.0   1.9
9   2021-10-25   xyz   1       1     3.6   1.5   3.1

我想加入代码（Cd）和测量编号（MeasNr）相同的行，并在变量（Var1，Var2，Var3）前加上它们的站号（StNr）。在此示例中，3 行彼此属于（StNr：1、2、3）。

所需的输出：

Cd   MeasNr  st1_Var1  st1_Var2  st1_Var3  st2_Var1  st2_Var2  st2_Var3  st3_Var1  st3_Var2  st3_Var3
-----------------------------------------------------------------------------------------------------
abc  1       NULL      NULL      NULL      1.5       3.7       4.6       NULL      2.6       2.8
abc  2       2.6       NULL      1.5       3.6       2.5       2.7       NULL      NULL      NULL
def  3       3.5       NULL      3.6       NULL      NULL      NULL      NULL      2.8       3.1
xyz  1       3.6       1.5       3.1       2.2       3.0       2.6       1.8       2.0       1.9

我尝试了以下 JOIN：

SELECT st1.Cd, st1.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 Measurements as st1
LEFT JOIN Measurements as st2 on (st1.Cd = st2.Cd and st1.MeasNr = st2.MeasNr) and (st2.StNr = 2)
LEFT JOIN Measurements as st3 on (st1.Cd = st3.Cd and st1.MeasNr = st3.MeasNr) and (st3.StNr = 3)
where st1.StNr = 1

然而，没有列出“abc”的第一个测量值（没有 StNr=1 的行，所以没有什么可加入的）。我怎样才能获得第一条记录呢？我也尝试了 FULL JOIN，但没有任何成功。

这是我的 SELECT 与 FULL JOIN：

SELECT 
  CASE
    WHEN st1.DtTm IS NOT NULL THEN st1.DtTm
    WHEN st2.DtTm IS NOT NULL THEN st2.DtTm
    WHEN st3.DtTm IS NOT NULL THEN st3.DtTm
  END AS DtTime,
  CASE
    WHEN st1.Cd IS NOT NULL THEN st1.Cd
    WHEN st2.Cd IS NOT NULL THEN st2.Cd
    WHEN st3.Cd IS NOT NULL THEN st3.Cd
  END AS Cd,
  CASE
    WHEN st1.MeasNr IS NOT NULL THEN st1.MeasNr
    WHEN st2.MeasNr IS NOT NULL THEN st2.MeasNr
    WHEN st3.MeasNr IS NOT NULL THEN st3.MeasNr
  END 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
    DtTm, Cd, MeasNr, Var1, Var2, Var3
    FROM Measurements
    WHERE StNr = 1) AS st1
  FULL JOIN (SELECT
    DtTm, Cd, MeasNr, Var1, Var2, Var3
    FROM Measurements
    WHERE StNr = 2) AS st2 ON (st1.Cd = st2.Cd AND st1.MeasNr = st2.MeasNr)
  FULL JOIN (SELECT
    DtTm, Cd, MeasNr, Var1, Var2, Var3
    FROM Measurements
    WHERE StNr = 3) AS st3 ON (st1.Cd = st3.Cd AND st1.MeasNr = st3.MeasNr)

使用这种方法，我有丢失的数据，但不是在一行中。

标签： sqlsql-serverjoinselect

解决方案

这看起来像是条件聚合的简单案例，也称为透视。

由于它是多列枢轴，因此使用起来要容易得多MAX(CASE WHEN

注意：不需要连接

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

SQL小提琴

推荐阅读