問題描述
假設我有這張表:
+----+-------+
| id | value |
+----+-------+
| 1 | 5 |
| 2 | 4 |
| 3 | 1 |
| 4 | NULL |
| 5 | NULL |
| 6 | 14 |
| 7 | NULL |
| 8 | 0 |
| 9 | 3 |
| 10 | NULL |
+----+-------+
我想編寫一個查詢，將任何 NULL 值替換為該列中表中不為空的最后一個值.
I want to write a query that will replace any NULL value with the last value in the table that was not null in that column.
我想要這個結果:
+----+-------+
| id | value |
+----+-------+
| 1 | 5 |
| 2 | 4 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 14 |
| 7 | 14 |
| 8 | 0 |
| 9 | 3 |
| 10 | 3 |
+----+-------+
如果以前的值不存在，則 NULL 是可以的.理想情況下，即使使用 ORDER BY，這也應該能夠正常工作.例如，如果我 ORDER BY [id] DESC:
If no previous value existed, then NULL is OK. Ideally, this should be able to work even with an ORDER BY. So for example, if I ORDER BY [id] DESC:
+----+-------+
| id | value |
+----+-------+
| 10 | NULL |
| 9 | 3 |
| 8 | 0 |
| 7 | 0 |
| 6 | 14 |
| 5 | 14 |
| 4 | 14 |
| 3 | 1 |
| 2 | 4 |
| 1 | 5 |
+----+-------+
如果我ORDER BY [value] DESC:
+----+-------+
| id | value |
+----+-------+
| 6 | 14 |
| 1 | 5 |
| 2 | 4 |
| 9 | 3 |
| 3 | 1 |
| 8 | 0 |
| 4 | 0 |
| 5 | 0 |
| 7 | 0 |
| 10 | 0 |
+----+-------+
我認為這可能涉及某種分析函數 - 以某種方式對值列進行分區 - 但我不確定在哪里查看.
I think this might involve some kind of analytic function - somehow partitioning over the value column - but I'm not sure where to look.
推薦答案
Itzik Ben-Gan 在此處介紹了最佳方法:最后一個非空謎題
The best way has been covered by Itzik Ben-Gan here:The Last non NULL Puzzle
下面是一個在我的系統上處理 1000 萬行并在 20 秒內完成的解決方案
Below is a solution which for 10 million rows and completes around in 20 seconds on my system
SELECT
id,
value1,
CAST(
SUBSTRING(
MAX(CAST(id AS binary(4)) + CAST(value1 AS binary(4)))
OVER (ORDER BY id
ROWS UNBOUNDED PRECEDING),
5, 4)
AS int) AS lastval
FROM dbo.T1;
此解決方案假定您的 id 列已編入索引
This solution assumes your id column is indexed
這篇關于T-SQL:用最新的非空值替換 NULL 的最佳方法?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！