如何仅使用最新值更新NULL值，但如果行中有任何值，请跳过它 [英] How to update only NULL value with recent value, but if there is any value in a row, skip it

问题描述

让我们考虑一下我有一张桌子： -



表名： - TblA

只有一列： - Val（varchar）< br $> b $ b

Val .................................. ......... val

------ O / P AS ---> -------------

NULL ............................ .......... NULL

NULL .............................. ........ NULL

NULL ................................ ...... NULL

P1 .................................. .......... P1

NULL .............................. ........ P1

NULL ................................ ...... P1

NULL .................................. .... P1

NULL .................................... ..P1

P2 ...................................... ...... P2

NULL .................................. .... P2

NULL .................................... ..P2

NULL ...................................... P2

P3 ........................................ .... P3

NULL .................................... ..P3

NULL ...................................... P3

NULL ...................................... P3

NULL ...................................... P3

NULL ...................................... P3

NULL ...................................... P3



谢谢



我的尝试：



我试过使用LEAD& LAG函数，但两者都在sql 2012中可用。不想为此使用游标。并且不想脚本如果第一行和值= NULL然后离开，不要更新。



智能解决方案，不使用LEAD& LAG，没有光标

Let's consider I have one table:-

Table Name :- TblA
Only one Column:- Val (varchar)

Val...........................................val
------O/P AS---> -------------
NULL......................................NULL
NULL......................................NULL
NULL......................................NULL
P1............................................P1
NULL......................................P1
NULL......................................P1
NULL......................................P1
NULL......................................P1
P2............................................P2
NULL......................................P2
NULL......................................P2
NULL......................................P2
P3............................................P3
NULL......................................P3
NULL......................................P3
NULL......................................P3
NULL......................................P3
NULL......................................P3
NULL......................................P3

Thanks

What I have tried:

I tried with Using LEAD & LAG function, but both are available in sql 2012. Don't want to use cursor for this. And not want to script as If 1st row and value =NULL then leave, don't update.

Smart solution without using LEAD & LAG, without cursor

推荐答案

我知道这看起来很复杂，但我会解释：

I know this look complex but I'll explain:

--Test Data i used
declare @mytest table(id int not null identity(1,1) primary key,val varchar(max) null)
insert into @mytest(val)
values 
(null),
(null),
('p1'),
(null),
(null),
(null),
(null),
('p2'),
(null),
(null),
('p2'),
(null),
(null),
(null),
('p3'),
(null) 
;

--Common table expressions (CTE)
with mycte as (
  select id, val, row_number () over (order by id) as roworder
  from @mytest)
, mycte2 as(
  select id, val, roworder
  from mycte
  where roworder = 1

  union all 

  select t.id, isnull(t.val,l.val), t.roworder
  from mycte t
  inner join mycte2 l on t.roworder = l.roworder+1
  )

  --select result
  select * from mycte2

如果您运行该区块，您将看到所需的结果。



我们可以忽略测试数据。这只是我试图重新创造这种情况。



公共表格表达式很有趣



让我们看看第一个：

If you run that block, you will see the desired outcome.

We can ignore the test data. That's just me trying to recreate the situation.

The Common Table Expressions are what are interesting

Lets look at the first one:

with mycte as (
  select id, val, row_number () over (order by id) as roworder
  from @mytest)

我创建的id是按顺序排列的，但我不知道你的ID是不是。我希望你拥有的数据是某种顺序（日期，id等），因为我们需要给列表一个连续的迭代数字，以使我们的生活变得更加容易。这就是row_number在这里做的事情。它按顺序放置项目并将它们编号为1,2,3，...，n-1，n。我们可以在称为递归CTE的东西中使用这个序列。



这是递归CTE：

The id's I create are in sequence, but I could not say if yours are. I hope that the data you have is in some sort of order (date, id, etc) because we need to give the list a sequential iterative number to make our live so much easier. That's what row_number does here. It puts the items in order and numbers them 1,2,3,..,,n-1,n. We can use this sequence in something called a recursive CTE.

Here is the recursive CTE:

mycte2 as(
  select id, val, roworder
  from mycte
  where roworder = 1

  union all 

  select t.id, isnull(t.val,l.val), t.roworder
  from mycte t
  inner join mycte2 l on t.roworder = l.roworder+1
  )

这是所有魔法发生的地方。在第一个'select'中，我们取列表中的第一项（roworder = 1）。然后我们将同一个表连接到此CTE。获取roworder为this + 1的下一个项目，但是它必须再次重新计算递归，依此类推，直到没有项目，其中t.roworder = l.roworder + 1.



每次我们将下一个项目附加到当前列表时，我们使用ISNULL（t.val，l.val），其中t表示'this'记录，l是'last'记录。如果此record.val为null，则使用last record.val。



我认为我没有这么好解释。试一试，看看它是否有效^ _ ^

This is where all the magic happens. In the first 'select' we take the first item in the list (roworder = 1). Then we union that with the same table joined to this CTE. Get the next item where roworder is this+1, but then it has to recalculate the recursion again, and so on until there is no item where t.roworder = l.roworder+1.

Each time we attach the next item to the current list we use ISNULL(t.val,l.val), where t represents 'this' record and l is 'last' record. If this record.val is null then use last record.val.

I don't think I explained this very well. Just try it out and see if it works ^_^

此解决方案在SQL 2008 R中测试并按预期工作

This solution is tested in SQL 2008 R and works as expected

with myrecords(rowid, val) as
(
  select ROW_NUMBER() over (order by (select 1)) as rowId , Value from NullManipulation
)
,myrecords1(rowid, val) as
(
  select ROW_NUMBER() over (order by (select 1)) as rowId , Value from NullManipulation
)
UPDATE mr1
SET val = mr.val
FROM myrecords1 mr1 , myrecords mr 
WHERE mr.rowid < mr1.rowid and not exists(select rowid from myrecords myr where myr.rowid > mr.rowid and myr.rowid < mr1.rowid and myr.val is not null ) and mr1.val is NULL  

创建表格

The table creation is

CREATE TABLE [dbo].[NullManipulation](
	[Value] [varchar](10) NULL
)

插入物是

插入NullManipulation值（NULL）;

插入NullManipulation值（NULL）;

插入NullManipulation值（NULL）;

插入NullManipulation值（'P1'）;

插入NullManipulation值（NULL）;

插入NullManipulation值（NULL）;

插入NullManipulation值（NULL）;

插入NullManipulation值（NUL L）;

插入NullManipulation值（'P2'）;

插入NullManipulation值（NULL）;

插入NullManipulation值（ NULL）;

插入NullManipulation值（NULL）;

插入NullManipulation值（'P3'）;

插入NullManipulation值（ NULL）;

插入NullManipulation值（NULL）;

插入NullManipulation值（NULL）;

插入NullManipulation值（NULL） ;

插入NullManipulation值（NULL）;

插入NullManipulation值（NULL）;



结果是

The inserts are
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values ('P1');
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values ('P2');
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values ('P3');
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);

The result is

Value
NULL
NULL
NULL
P1
P1
P1
P1
P1
P2
P2
P2
P2
P3
P3
P3
P3
P3
P3
P3

解决方案1相当不错，但这里有一个替代非递归版本。

它还使用两个CTE - 第二个在第一个上运行自联接，这是2012年之前SQL Server版本中LAG / LEAD的一个很好的替代品（你可能会发现它对其他有用）东西）

Solution 1 is rather nice, but here is an alternative non-recursive version.
It also uses two CTEs - the second one runs a self-join on the first which is a good alternative for LAG/LEAD in versions of SQL Server prior to 2012 (which you may find useful for other stuff)

;WITH CTE AS
(
	select id, val ,ROW_NUMBER() OVER(ORDER BY id) AS rn
	from TblA where val is not null
),CTE2 AS
(
	SELECT CTE.id, CTE.val, isnull(nxt.id, (select max(id) from TblA) + 1) as nxt
	FROM CTE
	LEFT OUTER JOIN CTE nxt ON nxt.rn = CTE.rn + 1
)
update t set val = t1.val
from TblA t
join CTE2 t1 on t.id > t1.id and t.id < t1.nxt

注意事项：

我在解决方案1中使用了与Andy Lanng相同的数据 - 即我们需要一个id列用数据来确定先前记录并按顺序排序。如果您无法更改表模式，请执行以下操作

Points to note:
I've used the same data as Andy Lanng in solution 1 - i.e. we need an id column with the data in order to determine the "previous" record and to order by. If you can't change the table schema then do something like this

create table #temp (id int identity(1,1), val varchar(max))
insert into #temp select * from TblA

并在上面的查询中使用#temp。



CTE只获取任何行的ID包含数据

CTE2获取包含数据的NEXT行的id，因此我们最终得到一个（临时）表格看起来像

and use #temp in the queries above.

CTE just gets the id of any rows that DO contain data
CTE2 gets the id of the NEXT row that contains data so we end up with a (temporary) table looking like

Id      Val     Nxt
4	P1	9
9	P2	13
13	P3	17

查询的最后部分链接间隙中的记录

The ON clause in the final part of the query links up the records in the gaps

这篇关于如何仅使用最新值更新NULL值，但如果行中有任何值，请跳过它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！