當可能丟失一兩個行時，如何在 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 with 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)
使用這種方法，我有丟失的資料，但不在一行中。
uj5u.com熱心網友回復：
這看起來像是條件聚合的一個簡單案例，也稱為旋轉。
由于它是一個多列樞軸，因此使用起來要容易得多 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小提琴
uj5u.com熱心網友回復：
也許這不是一個漂亮的方式，但它有效：
with MyTable (Cd, MeasNr, st1_Var1, st1_Var2, st1_Var3, st2_Var1, st2_Var2, st2_Var3, st3_Var1, st3_Var2, st3_Var3)
as
(
select Cd, MeasNr, Var1 as st1_Var1, Var2 as st1_Var2, Var3 as st1_Var3, null as st2_Var1, null as st2_Var2, null as st2_Var3, null as st3_Var1, null as st3_Var2, null as st3_Var3 from Measurements st1 where StNr = 1
union
select Cd, MeasNr, null, null, null, Var1 as st2_Var1, Var2 as st2_Var2, Var3 as st2_Var3, null, null, null from Measurements st1 where StNr = 2
union
select Cd, MeasNr, null, null, null, null, null, null, Var1 as st3_Var1, Var2 as st3_Var2, Var3 as st3_Var3 from Measurements st1 where StNr = 3
)
select Cd, MeasNr, sum(st1_Var1) st1_Var1, sum(st1_Var2) st1_Var2, sum(st1_Var3) st1_Var3,
sum(st2_Var1) st2_Var1, sum(st2_Var2) st2_Var2, sum(st2_Var3) st2_Var3,
sum(st3_Var1) st3_Var1, sum(st3_Var2) st3_Var2, sum(st3_Var3) st3_Var3
from MyTable
group by Cd, MeasNr
order by Cd, MeasNr
我在這里粘貼了帶有變數表的代碼，也許它對測驗有用：
declare @Measurements as table (
id int,
DtTm datetime,
Cd char(3),
MeasNr int,
StNr int,
Var1 decimal(5,1),
Var2 decimal(5,1),
Var3 decimal(5,1)
)
insert into @Measurements values (1, '25/10/21', 'abc', 1, 2, 1.5, 3.7, 4.6)
insert into @Measurements values (2, '25/10/21', 'abc', 1, 3, null, 2.6, 2.8)
insert into @Measurements values (3, '25/10/21', 'def', 3, 1, 3.5, null, 3.6)
insert into @Measurements values (4, '25/10/21', 'abc', 2, 1, 2.6, null, 1.5)
insert into @Measurements values (5, '25/10/21', 'def', 3, 3, null, 2.8, 3.1)
insert into @Measurements values (6, '25/10/21', 'abc', 2, 2, 3.6, 2.5, 2.7)
insert into @Measurements values (7, '25/10/21', 'xyz', 1, 2, 2.2, 3.0, 2.6)
insert into @Measurements values (8, '25/10/21', 'xyz', 1, 3, 1.8, 2.0, 1.9)
insert into @Measurements values (9, '25/10/21', 'xyz', 1, 1, 3.6, 1.5, 3.1);
with MyTable (Cd, MeasNr, st1_Var1, st1_Var2, st1_Var3, st2_Var1, st2_Var2, st2_Var3, st3_Var1, st3_Var2, st3_Var3)
as
(
select Cd, MeasNr, Var1 as st1_Var1, Var2 as st1_Var2, Var3 as st1_Var3, null as st2_Var1, null as st2_Var2, null as st2_Var3, null as st3_Var1, null as st3_Var2, null as st3_Var3 from @Measurements st1 where StNr = 1
union
select Cd, MeasNr, null, null, null, Var1 as st2_Var1, Var2 as st2_Var2, Var3 as st2_Var3, null, null, null from @Measurements st1 where StNr = 2
union
select Cd, MeasNr, null, null, null, null, null, null, Var1 as st3_Var1, Var2 as st3_Var2, Var3 as st3_Var3 from @Measurements st1 where StNr = 3
)
select Cd, MeasNr, sum(st1_Var1) st1_Var1, sum(st1_Var2) st1_Var2, sum(st1_Var3) st1_Var3,
sum(st2_Var1) st2_Var1, sum(st2_Var2) st2_Var2, sum(st2_Var3) st2_Var3,
sum(st3_Var1) st3_Var1, sum(st3_Var2) st3_Var2, sum(st3_Var3) st3_Var3
from MyTable
group by Cd, MeasNr
order by Cd, MeasNr
uj5u.com熱心網友回復：
對于不支持公用表運算式 WITH 的 MySQL < v8：
SELECT Cd, MeasNr,
SUM(c11) AS st1_Var1, SUM(c12) AS st1_Var2, SUM(c13) AS st1_Var3,
SUM(c21) AS st2_Var1, SUM(c22) AS st2_Var2, SUM(c23) AS st2_Var3,
SUM(c31) AS st3_Var1, SUM(c32) AS st3_Var2, SUM(c33) AS st3_Var3
FROM (
SELECT DISTINCT Cd, MeasNr,
Var1 as c11, Var2 as c12, Var3 as c13,
NULL as c21, NULL as c22, NULL as c23,
NULL as c31, NULL as c32, NULL as c33
FROM Measurements WHERE StNr = 1
UNION
SELECT DISTINCT Cd, MeasNr,
NULL as c11, NULL as c12, NULL as c13,
Var1 as c21, Var2 as c22, Var3 as c23,
NULL as c31, NULL as c32, NULL as c33
FROM Measurements WHERE StNr = 2
UNION
SELECT DISTINCT Cd, MeasNr,
NULL as c11, NULL as c12, NULL as c13,
NULL as c21, NULL as c22, NULL as c23,
Var1 as c31, Var2 as c32, Var3 as c33
FROM Measurements WHERE StNr = 3
) t1
GROUP BY Cd, MeasNr
ORDER BY Cd, MeasNr
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/338400.html
標籤：sql sql-server 加入 选择