SQL Server 2012，根据下一条记录更新记录

I'm struggling to find a solution to update (not just select) SQL table records based on previous values. I can get the script to populate the immediate subsequent record with the LAG() function, but when I have the same values in a row (like 'CC' below) I can't get it to populate with the next value that's not the same as the current value.

我很难找到一个解决方案，根据以前的值更新（而不仅仅是选择）SQL表记录。我可以使用LAG（）函数来获取脚本以填充紧接的后续记录，但是当我在一行中具有相同的值（如下面的“CC”）时，我无法使用下一个值来填充它不是与当前值相同。

It would also be helpful to be able to add CASE/WHEN condition so that only values with the same BaseID are evaluated. Any help would be greatly appreciated.

能够添加CASE / WHEN条件以便仅评估具有相同BaseID的值也是有帮助的。任何帮助将不胜感激。

Here is my desired result:

这是我想要的结果：

BaseID  Value  Date        NextValue
1       AA     2017-10-01  BB
1       BB     2017-10-02  CC
1       CC     2017-10-03  DD
1       CC     2017-10-03  DD
1       CC     2017-10-03  DD
1       DD     2017-10-04  NULL
2       EE     2017-10-01  FF
2       FF     2017-10-02  GG
2       GG     2017-10-03  NULL

2 个解决方案

#1

Get the distinct baseid,value,date combinations and use lead to get the next value in a cte and use itto update.

获取不同的基数，值，日期组合并使用线索获取cte中的下一个值并使用它进行更新。

with cte as (select t1.baseid,t1.value,t1.nextvalue,t2.nxt_value
             from tbl t1 
             left join (select t.*,lead(value) over(partition by baseid order by datecol) as nxt_value 
                        from (select distinct baseid,datecol,value from tbl) t
                       ) t2 
             on t1.baseid=t2.baseid and t1.datecol=t2.datecol and t1.value=t2.value
             )
update cte set nextvalue=nxt_value

This assumes there can't be multiple values for a given baseid,date combination.

这假设给定的baseid，date组合不能有多个值。

#2

Here is a working example using DENSE_RANK as another option.

这是一个使用DENSE_RANK作为另一个选项的工作示例。

declare @Something table
(
    BaseID int
    , MyValue char(2)
    , MyDate date
    , NextValue char(2)
)

insert @Something
(
    BaseID
    , MyValue
    , MyDate
) VALUES
(1, 'AA', '2017-10-01')
, (1, 'BB', '2017-10-02')
, (1, 'CC', '2017-10-03')
, (1, 'CC', '2017-10-03')
, (1, 'CC', '2017-10-03')
, (1, 'DD', '2017-10-04')
, (2, 'EE', '2017-10-01')
, (2, 'FF', '2017-10-02')
, (2, 'GG', '2017-10-03')
;

with SortedResults as
(
    select *
        , DENSE_RANK() over(partition by BaseID order by BaseID, MyDate ) as MyRank
    from @Something
)

update sr set NextValue = sr2.MyValue
from SortedResults sr
join SortedResults sr2 on sr2.MyRank - 1 = sr.MyRank and sr.BaseID = sr2.BaseID


select *
from @Something

#1