T-SQL 将当前行更新到下一行的值问题

2021-09-10 00:00:00 sql tsql sql-server dml

我正在寻找一些关于在特定场景中哪种更新最适合 t-sql DML 的建议.

I am looking for some advice on what type of update would be the best in a certain scenario for t-sql DML.

遇到数据问题，结束日期比当前记录的开始日期早一天，并且需要将多个实体的结束日期设置为下一行的开始日期

came into a data issue where end dates are one day before the start date on a current record, and need to set the end date to the start date of the next in row for multiple entities

例如

rowid     entity    id  record_type start_date  end_date
214       250       1   H           2015-01-01  2014-12-31
329       250       1   H           2015-04-25  2015-04-24
533       250       1   C           2015-11-01  NULL
11        250       2   H           2015-06-01  2014-05-29
292       250       2   H           2015-09-11  2015-09-10
987       250       2   C           2015-10-01  NULL

我需要做的是将第一条记录的 end_date 更新为下一条记录的 startdate - 每个员工/实体都为 1.

What I need to do is update the first record end_date to the next record startdate - 1 on each employee/entity.

目前，受此影响的实体超过 5000 个，因此我试图以某种方式在每条记录上更新此信息，以节省时间.

Currently, there are over 5K entities affected by this, so I am trying to get this updated on each record somehow to save time on this.

我能做但要花很多时间的就是1. 将所有公司的历史记录的最大行数合并为一个数2.为总行数创建相同数量的临时表3. 将最小开始日期值插入到第一个临时表中4.将不在临时表1中的最小值插入到表2中，依此类推5. 然后将临时表 1 的结束日期更新为临时表 2 的开始日期 -1 天6. 从那里，对实际表运行更新，为每个临时表运行多个更新语句，在 rowid 上加入.

What I can do but is taking a lot of time, is to 1. get a max rowcount of history records for all companies into one number 2. create the same amount of temp tables for the number of total rows 3. insert minimum start date values into first temp table 4. insert minimum value not in temp table 1 into table 2, and so on 5. then update temp table 1's end date to temptable 2's startdate -1 day 6. from there, run an update on the actual table with multiple update statements for each temp table, joined on rowid.

最终输出如下:

rowid     entity    id  record_type start_date  end_date
214       250       1   H           2015-01-01  2014-04-24
329       250       1   H           2015-04-25  2015-10-31
533       250       1   C           2015-11-01  NULL
11        250       2   H           2015-06-01  2014-09-10
292       250       2   H           2015-09-11  2015-9-31
987       250       2   C           2015-10-01  NULL

除了我的一长串临时表/更新之外的任何建议将不胜感激！我在想一些可能是光标的东西，但我不太确定这是否是为这种情况编写更新的更快方法.

Any suggestions besides my long list of temp tables/updates would be greatly appreciated! I was thinking something along the lines of possibly a cursor, but I am not too sure if this would be a quicker way of writing an update for this scenario.

推荐答案

我认为可更新的 CTE 是要走的路.在 SQL Server 2012+ 中，您可以使用 lead():

I think updatable CTEs are the way to go. In SQL Server 2012+, you can use lead():

with toupdate as (
      select t.*,
             lead(start_date) over (partition by entity order by start_date) as next_start_date
      from t
     )
update toupdate
     set end_date = dateadd(day, -1, next_start_date)
     where end_date = dateadd(day, -1, start_date);

相关文章