对于此特定方案，替换表的特定列中的值的最有效方法是什么? [英] What is the most effcient way to replace values in a specific column of a table for this specific scenario?

问题描述

我正在使用 SQL Server 2014 ，并且我的数据库中有一个名为 t1 的表(下面仅显示了两列):

I am using SQL Server 2014 and I have a table in my database called t1 (extract of only 2 columns shown below):

 ResaID       StayDate
 100          2020-02-03
 100          2020-02-04
 100          2020-02-05
 120          2020-04-06
 120          2020-04-07
 120          2020-04-08
 120          2020-04-09
 120          2020-04-10

我需要根据以下信息更改 StayDate 列中的日期(摘录内容与提供的完全一样):

I need to change the dates in the StayDate column based on the following information (extract shown exactly as provided):

 ID        StartDate       EndDate
 100       2020-06-04      2020-06-06
 120       2021-03-01      2021-03-05

我已经开始按如下方式编写T-SQL查询(但是它变得非常乏味，因为我必须为100多个ResaID做它！):

I have started writing my T-SQL query as follows (but it is getting quite tedious as I have to do it for more than 100 ResaID!):

USE MyDatabase

UPDATE t1

SET StayDate = CASE WHEN ResaID = 100 and StayDate = '2020-02-03' THEN '2020-06-04'
WHEN ResaID = 100 and StayDate = '2020-02-04' THEN '2020-06-05'
WHEN ResaID = 100 and StayDate = '2020-02-05' THEN '2020-06-06'
...    

ELSE StayDate

END

有没有更有效的方法来解决这个问题?

Is there a more efficient way to tackle this problem?

推荐答案

您可以使用递归方法:

with r_cte as (
     select id, convert(date, startdate) as startdate, convert(date, enddate) as enddate
     from ( values (100, '2020-06-04', '2020-06-06'), 
                   (120, '2021-03-01', '2021-03-03')
          ) t(id, startdate, enddate)
     union all
     select id, dateadd(day, 1, startdate), enddate
     from cte c
     where startdate < enddate
), r_cte_seq as (
     select r.cte.*, row_number() over(partition by id order by startdate) as seq
     from r_cte 
), cte_seq as (
     select t1.*, row_number() over (partition by ResaID order by staydate) as seq
     from t1
)
update cs
       set cs.staydate = rc.startdate
from cte_seq cs inner join
     r_cte_seq rc
     on rc.id = cs.ResaID and rc.seq = cs.seq;

这篇关于对于此特定方案，替换表的特定列中的值的最有效方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！