如何将前导空值替换为一行中的第一个非空值？

Question

我将尽量避免在这里描述背景。我现在有一个查询结果（不是表），其中包含如下行：

ID      SP1     SP2     SP3     SP4     SP5    SP6     SP7     SP8
1       null    null    2500    1400    700    null    null    null

非空值部分周围可能有前导和/或尾随空值（这实际上代表了递减过程）。我想要的是这样的：

ID      SP1     SP2     SP3     SP4     SP5    SP6     SP7     SP8
1       2500    2500    2500    1400    700    0       0       0

这意味着，用第一个非空值替换前导空值，用 0 替换尾随空值。
请指教。我正在研究 SQL Server 2000。

Answer 1

SELECT
    ID,
    COALESCE(SP1,SP2,SP3,SP4,SP5,SP6,SP7,SP8,0) as SP1,
    COALESCE(SP2,SP3,SP4,SP5,SP6,SP7,SP8,0) as SP2,
    COALESCE(SP3,SP4,SP5,SP6,SP7,SP8,0) as SP3,
    COALESCE(SP4,SP5,SP6,SP7,SP8,0) as SP4,
    COALESCE(SP5,SP6,SP7,SP8,0) as SP5,
    COALESCE(SP6,SP7,SP8,0) as SP6,
    COALESCE(SP7,SP8,0) as SP7,
    COALESCE(SP8,0) as SP8
FROM
    (<your existing query>) t

COALESCE 接受多个表达式，并返回第一个非空值价值。

Answer 2

您可能应该重新构建您的架构。每当您在单行中有看起来像数组的内容时，通常最好将其拆分为单独的行。

但是，假设您坚持使用当前模式，我只会选择一个简单的：（

start transaction;
update TBL set SP8 =   0 where SP8 is null;
update TBL set SP7 = SP8 where SP7 is null;
update TBL set SP6 = SP7 where SP6 is null;
update TBL set SP5 = SP6 where SP5 is null;
update TBL set SP4 = SP5 where SP4 is null;
update TBL set SP3 = SP4 where SP3 is null;
update TBL set SP2 = SP3 where SP2 is null;
update TBL set SP1 = SP2 where SP1 is null;
commit;

如果需要，替换为正确的 SQL Server 事务语法）。

如果您不想实际更改底层数据，您可以使用视图，但它可能很丑陋，并且您可能希望选择在您使用的任何应用程序中进行转换来执行 SQL。

一种可能性，但我强烈敦促您不要这样做，并且可能有更好的特定于供应商的方法：

• 在表上创建一个视图 view8 ，使所有列保持不变除了 SP8 之外，它会变成 coalesce(sp8,0) （或者任何 SQL Server 等值 - SP8，如果它不为 NULL，否则为 0）。
• 在视图 view8 上创建一个视图 view7，该视图使所有列保持不变，除了 SP7 变为 coalesce(sp7,sp8).
• 在视图 view7 上创建一个视图 view6，该视图使所有列保持不变，除了 SP6 变为 coalesce(sp6,sp7).
• 巴拉，巴拉，巴拉。
• 在视图 view2 上创建一个视图 view1，该视图使所有列保持不变，除了 SP1 变为 coalesce(sp1,sp2).
• 使用视图view1。

正如我所说，这是一个巨大的拼凑，请出于对您信仰的任何神的热爱，不要使用它。但有时需要决定我们的行动，所以我把它放在那里以防万一。

完全关心，不承担任何责任，请自行测试（和分析）。

---

而且，在发布该内容并发现达米安有一个更紧凑的版本后，我还想提供以下内容。

有时牺牲空间换取时间很有用（以占用更多磁盘空间为代价加快速度）。

您可以创建另外 8 列（MORPHSP1 到 MORPHSP8），以存储我在第一个解决方案中建议的变形值。

这通常会违反 3NF，但如果你做两件事实际上是可以的：（1）理解后果； (2) 减少数据不一致的可能性。

通过使用插入/更新触发器，您实际上可以保证数据保持一致。

每当行发生更改时，让触发器执行以下操作。

set MORPHSP8 to coalesce (SP8,0)
set MORPHSP7 to coalesce (SP7,MORPHSP8)
set MORPHSP6 to coalesce (SP6,MORPHSP7)
set MORPHSP5 to coalesce (SP5,MORPHSP6)
set MORPHSP4 to coalesce (SP4,MORPHSP5)
set MORPHSP3 to coalesce (SP3,MORPHSP4)
set MORPHSP2 to coalesce (SP2,MORPHSP3)
set MORPHSP1 to coalesce (SP1,MORPHSP2)

这样，您只需在数据更改时产生成本，而不是每次使用数据时都会产生成本。在读取次数超过写入次数（占绝大多数）的表上，这可以带来令人印象深刻的性能改进。