用以前的记录值更新最新记录

Question

我有一个名为审计的表，它有一个CompanyId、Date、AuditNumber字段，每个公司都可以有很多审计，而AuditNumber字段跟踪了所述公司的审计数量。

我试图用以前的日期+5年来更新所有最新的审计记录日期，因此假设CompanyId 12345有3次审计，我想更新第3次审计(第3次审计是最新的)记录日期，第二次审计记录日期+5年后，等等。基本上，对所有最新的记录都这样做。

到目前为止，我所得到的是尝试使用while循环来完成这个任务，但是我被困住了，因为它并没有做我想要它做的事情……

DECLARE @counter INT = 1;
WHILE(@counter <= (SELECT COUNT(*) FROM Audits WHERE AuditNumber > 1)
BEGIN
    UPDATE Audits
    SET Date = CASE
                   WHEN AuditNumber > 1 THEN (SELECT TOP 1 DATEADD(YEAR, 5, Date) FROM Audits WHERE AuditNumber < (SELECT(MAX(AuditNumber) FROM Audits))
                   END
    WHERE AuditNumber > 1
    SET @counter = @counter + 1
END

我不是SQL方面的专家，但这只是因为SELECT TOP(1)，它可以用它可以找到的第一个日期来更新日期，但是如果我不把它放在顶部(1)，子查询会返回超过一个记录，因此它会发出抱怨。

任何帮助都将不胜感激。

谢谢!

Answer 1

不需要程序和循环。为此，我建议使用窗口函数和可更新的cte：

with cte as (
    select date, 
        row_number() over(partition by company order by auditnumber desc) rn,
        lag(date) over(partition by company order by auditnumber) lag_date
    from audits
)
update cte 
set date = dateadd(year, 5, lag_date) 
where rn = 1 and lag_date is not null

公共表表达式通过下降审计编号对具有相同公司的记录进行排序，并检索上一次审核的日期。外部查询在每个组的顶部记录上筛选，并将日期更新到上一个日期之后的5年。

当一家公司只有一次审计时，你并没有告诉你该怎么做。我为不更新这些行添加了一个条件(如果有的话)。

Answer 2

必须先将row_number添加到result_tbl中，然后在Al.CompanyId=A2.CompanyId和Al.IND=1和A2.IND=2上加入result_tbl，现在在一条记录中有最新的记录和以前的记录，并且可以更新原始表。

WITH A AS
(
    SELECT *,ROW_NUMBER(PARTITION BY CompanyId ORDER BY AuditNumber DESC) IND FROM Audits
),B AS 
(
    SELECT Al.CompanyId,A1.AuditNumber,A2.[DATE] FROM A A1 INNER JOIN A A2 ON Al.CompanyId=A2.CompanyId AND Al.IND=1 AND A2.IND=2
)UPDATE _Audits SET _Audits.[Date]= DATEADD(YEAR,5,B.[DATE]) FROM
B LEFT JOIN Audits _Audits ON B.CompanyId=_Audits.CompanyId AND B.AuditNumber=_Audits.AuditNumber