問題描述

假設(shè)我有這張表:

+----+-------+
| 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.

我想要這個結(jié)果:

+----+-------+
| 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，這也應(yīng)該能夠正常工作.例如，如果我 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 |
+----+-------+

我認(rèn)為這可能涉及某種分析函數(shù) - 以某種方式對值列進(jìn)行分區(qū) - 但我不確定在哪里查看.

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

下面是一個在我的系統(tǒng)上處理 1000 萬行并在 20 秒內(nèi)完成的解決方案

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

這篇關(guān)于T-SQL:用最新的非空值替換 NULL 的最佳方法?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！