php - 在数据透视查询中查找最后一个 "Non-Null"值

Question

我有以下查询:

 SELECT * FROM 
                (
                    SELECT unix_timestamp, input_raw, tag_id 
                    from [200030].[dbo].inputs
                    WHERE inputs.date_time > dateadd(day,-1,getdate())
                    AND
                    (tag_id = 92164 or tag_id = 92149)
                ) src
                pivot
                (
                    max(input_raw)
                    FOR tag_id IN ([92164], [92149])
                ) piv
                ORDER by unix_timestamp DESC

这很棒而且有效。它给了我结果:

但是，我希望查询为我再做一件事。

每当出现“NULL”结果时，我希望查询将“NULL”替换为列中最后一个“非 NULL”值。

例如，在“92164”列中看到的第一个 NULL 将替换为“211”。

此外，一行中可能会有多个“NULL”，因此查询需要继续向上查询该列，直到找到一个非 NULL。

我已经能够使用 php 完成此操作。将结果放入 2D 关联数组并运行一个查找空值的函数，然后循环查找最后一个非 NULL，但如果可能的话，我真的想在 SQL 中全部完成。我更愿意使用

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
  //code     
}

比分配几个数组的方法。

有什么帮助吗？

谢谢

//编辑

忘记补充一点，这仅适用于空值上方有任何非空值的情况。例如，如果第一行是 NULL 是可以接受的。

Answer 1

您可以将数据透视查询放在 CTE 中，并在确定要显示的值时重用 CTE。您的数据仅在第一列中有空值，但这里有一个版本可以处理两列中的空值。

WITH C AS
(
  SELECT * 
  FROM (
       SELECT unix_timestamp, input_raw, tag_id 
       FROM inputs
       WHERE date_time > dateadd(day,-1,getdate()) AND
             (tag_id = 92164 OR tag_id = 92149)
       ) src
  PIVOT
       (
       MAX(input_raw)
       FOR tag_id IN ([92164], [92149])
       ) piv
)
SELECT C1.unix_timestamp,
       (
       SELECT TOP(1) C2.[92164] 
       FROM C AS C2
       WHERE C1.unix_timestamp <= C2.unix_timestamp AND
             C2.[92164] IS NOT NULL
       ORDER BY C2.unix_timestamp
       ) AS [92164],
       (
       SELECT TOP(1) C2.[92149] 
       FROM C AS C2
       WHERE C1.unix_timestamp <= C2.unix_timestamp AND
             C2.[92149] IS NOT NULL
       ORDER BY C2.unix_timestamp
       ) AS [92149] 
FROM C AS C1
ORDER by C1.unix_timestamp DESC;

如果您遇到性能问题，将 CTE 的结果存储在具有有用的聚集键索引的临时表中可能会更快。

CREATE TABLE #C
(
  unix_timestamp int PRIMARY KEY,
  [92164] int,
  [92149] int
);

INSERT INTO #C
SELECT *
FROM (
      SELECT unix_timestamp, input_raw, tag_id 
      FROM inputs
      WHERE date_time > dateadd(day,-1,getdate()) AND
            (tag_id = 92164 OR tag_id = 92149)
      ) src
PIVOT
      (
      MAX(input_raw)
      FOR tag_id IN ([92164], [92149])
      ) piv;

SELECT C1.unix_timestamp,
       (
       SELECT TOP(1) C2.[92164] 
       FROM #C AS C2
       WHERE C1.unix_timestamp <= C2.unix_timestamp AND
             C2.[92164] IS NOT NULL
       ORDER BY C2.unix_timestamp
       ) AS [92164],
       (
       SELECT TOP(1) C2.[92149] 
       FROM #C AS C2
       WHERE C1.unix_timestamp <= C2.unix_timestamp AND
             C2.[92149] IS NOT NULL
       ORDER BY C2.unix_timestamp
       ) AS [92149]
FROM #C AS C1
ORDER by C1.unix_timestamp DESC;

DROP TABLE #C;