問(wèn)題描述
我正在尋找一些關(guān)于在特定場(chǎng)景中哪種更新最適合 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.
遇到數(shù)據(jù)問(wèn)題，結(jié)束日期比當(dāng)前記錄的開(kāi)始日期早一天，并且需要將多個(gè)實(shí)體的結(jié)束日期設(shè)置為下一行的開(kāi)始日期
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 - 每個(gè)員工/實(shí)體都為 1.
What I need to do is update the first record end_date to the next record startdate - 1 on each employee/entity.
目前，受此影響的實(shí)體超過(guò) 5000 個(gè)，因此我試圖以某種方式在每條記錄上更新此信息，以節(jié)省時(shí)間.
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.
我能做但要花很多時(shí)間的就是1. 將所有公司的歷史記錄的最大行數(shù)合并為一個(gè)數(shù)2.為總行數(shù)創(chuàng)建相同數(shù)量的臨時(shí)表3. 將最小開(kāi)始日期值插入到第一個(gè)臨時(shí)表中4.將不在臨時(shí)表1中的最小值插入到表2中，依此類(lèi)推5. 然后將臨時(shí)表 1 的結(jié)束日期更新為臨時(shí)表 2 的開(kāi)始日期 -1 天6. 從那里，對(duì)實(shí)際表運(yùn)行更新，為每個(gè)臨時(shí)表運(yùn)行多個(gè)更新語(yǔ)句，在 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
除了我的一長(zhǎng)串臨時(shí)表/更新之外的任何建議將不勝感激！我在想一些可能是光標(biāo)的東西，但我不太確定這是否是為這種情況編寫(xiě)更新的更快方法.
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.
推薦答案
我認(rèn)為可更新的 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);
這篇關(guān)于T-SQL 將當(dāng)前行更新到下一行的值問(wèn)題的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！