我想更新每个团队的第一行之后的所有行 [英] I want to update all the rows after the first row for each Team

问题描述

我想更新每个团队的第一行之后的所有行.

I want to update all the rows after the first row for each Team.

表名:测试

ID , Team , StartTime, EndTime, TotalTime
1....... A.........18:00.........20:00..........2:00
2....... B.........18:00.........20:00..........2:00
3........A.........18:00.........20:00..........2:00
4........F.........18:00.........20:00..........2:00
5........B.........18:00.........20:00..........2:00

 UPDATE TEST SET StartTime  =  DateAdd(SECOND, - ProjectedTime * 60, EndTime)

所以在这里，我想更新第 3 行和第 5 行(因为它们不是基于团队的第一条记录)

So here, I want to update row 3 and row 5 (as they are not first records based on team)

我如何实现这一目标?

推荐答案

您可以将 CTE 与 row_number() 一起使用:

You can use a CTE with row_number():

with toupdate as (
      select t.*, row_number() over (partition by team order by id) as seqnum
      from test
     )
update toupdate
    set StartDate = DateAdd(SECOND, - ProjectedTime * 60, EndDate)
    where seqnum > 1; 

这篇关于我想更新每个团队的第一行之后的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！