我有一个像这样的表结构:
我需要获取每列的最新值!= NULL。我当前的方法是使用这样的UNION
语句:
SELECT TOP 1 testlong1_min, testlong1_max, NULL, NULL, NULL, NULL, NULL, NULL FROM Grenzwerte WHERE testlong1_min IS NOT NULL AND testlong1_max IS NOT NULL UNION
SELECT TOP 1 NULL, NULL, testlong2_min, testlong2_max, NULL, NULL, NULL, NULL FROM Grenzwerte WHERE testlong2_min IS NOT NULL AND testlong2_max IS NOT NULL UNION
SELECT TOP 1 NULL, NULL, NULL, NULL, testlong3_min, testlong3_max, NULL, NULL FROM Grenzwerte WHERE testlong3_min IS NOT NULL AND testlong3_max IS NOT NULL UNION
SELECT TOP 1 NULL, NULL, NULL, NULL, NULL, NULL, testlong4_min, testlong4_max FROM Grenzwerte WHERE testlong4_min IS NOT NULL AND testlong4_max IS NOT NULL
这似乎不起作用,我的结果是空的。我还考虑过对每个字段执行1个查询,但是与UNION语句相比,我想这会增加太多开销。
问题:
有没有办法浓缩列并在一行中返回最新值?
编辑
使用Parodo的建议,我现在得到了这个结果,现在我需要将这些行合并为一个。
在下面的语句中,每个CTE仅具有所有各自的非null对,并且还将获得一个行号,其中最新ID的值为1。因此,我仅基于每个CTE的行号获得最新的ID,因此每个只有一排。这样,CROSS JOINING将导致单行具有所需的结果。
;WITH testLong1CTE AS
(
SELECT testlong1_min, testlong1_max, ROW_NUMBER()OVER(ORDER BY ID DESC) AS rn
FROM Table
WHERE testlong1_min IS NOT NULL AND testlong1_max IS NOT NULL
),testLong2CTE AS
(
SELECT testlong2_min, testlong2_max, ROW_NUMBER()OVER(ORDER BY ID DESC) AS rn
FROM Table
WHERE testlong2_min IS NOT NULL AND testlong2_max IS NOT NULL
),testLong3CTE AS
(
SELECT testlong3_min, testlong3_max, ROW_NUMBER()OVER(ORDER BY ID DESC) AS rn
FROM Table
WHERE testlong3_min IS NOT NULL AND testlong3_max IS NOT NULL
),testLong4CTE AS
(
SELECT testlong4_min, testlong4_max, ROW_NUMBER()OVER(ORDER BY ID DESC) AS rn
FROM Table
WHERE testlong4_min IS NOT NULL AND testlong4_max IS NOT NULL
)
SELECT testlong1_min,
testlong1_max,
testlong2_min,
testlong2_max,
testlong3_min,
testlong3_max,
testlong4_min,
testlong4_max
FROM (SELECT testlong1_min, testlong1_max FROM testLong1CTE WHERE rn = 1) AS T1
CROSS JOIN (SELECT testlong2_min, testlong2_max FROM testLong2CTE WHERE rn = 1) AS T2
CROSS JOIN (SELECT testlong3_min, testlong3_max FROM testLong3CTE WHERE rn = 1) AS T3
CROSS JOIN (SELECT testlong4_min, testlong4_max FROM testLong4CTE WHERE rn = 1) AS T4
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句