sql - SQL 查询更新先前传输的重复记录

Question

请求某人帮助解决以下关于 sql server 查询的问题。我想根据下表更新备注栏。目前我正在将备注栏更新为“发现重复记录”，但现在我想添加之前传输的起息日。

ID  RefNo       Value Date  Remarks
1   MFX93838    20-05-2020
2   MFX93838    25-05-2020  Record is already transmitted on 20-05-2020
3   MOU93838    22-05-2020   
4   MOU93838    23-05-2020  Record is already transmitted on 22-05-2020

标签： sqlsql-server

Answer 1

您可以使用lag(). 对于一个update：

with toupdate as (
      select t.*, lag(date) over (partition by refno order by date) as prev_date
      from t
     )
update toupdate
    set remarks = concat('Record is already transmitted on ', prev_date)
    where prev_date is not null;

您可能想要使用convert()或format()将日期转换为特定格式。