基于另一个SQL查询替换的空值

Question

因此，我使用ROW_NUMBER() OVER(PARTITION BY)查询返回数据和行号，并将其放入一个临时表中。初始输出如截图所示：

​

。

从这里开始，我需要在bt_newlabel列中分别替换空值。因此，第1-4行将在进行中，5-9将是承保，10-13将是实现，以此类推。

我正在碰壁，试图确定如何做到这一点。感谢你的任何帮助或意见，我将如何进行这一点。

Answer 1

一种方法是分配组，然后指定值。例如：

select t.*, max(bt_newlabel) over (partition by grp) as new_newlabel
from (select t.*, count(bt_newlabel) over (order by bt_stamp) as grp
      from t
     ) t;

该组只是先前在数据中看到的已知值的数量。

您可以使用以下命令更新该字段：

with toupdate as (
      select t.*, max(bt_newlabel) over (partition by grp) as new_newlabel
      from (select t.*, count(bt_newlabel) over (order by bt_stamp) as grp
            from t
           ) t
     )
update toupdate
    set bt_newlabel = new_newlabel
    where bt_newlabel is null;

Answer 2

如果我理解您正在尝试做什么，这是您需要在临时表上执行的更新类型：

--This will update rows 1-4 to 'Pre-Underwritting'

UPDATE temp_table SET bt_newlabel = 'Pre-Underwritting'
WHERE rownumber between 
1 AND (SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Pre-Underwritting');

--This will update rows 5-9 to 'Underwritting'

UPDATE temp_table SET bt_newlabel = 'Underwritting'
WHERE rownumber between 
(SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Pre-Underwritting')
AND
(SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Underwritting');

--This will update rows 10-13 to 'Implementation'

UPDATE temp_table SET bt_newlabel = 'Implementation'
WHERE rownumber between 
(SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Underwritting')
AND
(SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Implementation');

我做了一个可以正常工作的提琴来查看结果：http://sqlfiddle.com/#!18/1cae2/1/3