更新sql server中的另一行值

cgh8pdjw 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(421)

我有一个表，上面有名称、位置、开始日期和结束日期，如下所示：

+------+----------+-----------+-----------+-----------+
| name | location | startdate |  endate   | is_active |
+------+----------+-----------+-----------+-----------+
| A    | delhi    | 3/26/2019 | 3/26/2019 |         1 |
| A    | delhi    | 3/27/2019 | 3/27/2019 |         1 |
| A    | delhi    | 3/28/2019 | 3/28/2019 |         1 |
| A    | delhi    | 3/31/2019 | 3/31/2019 |         1 |
+------+----------+-----------+-----------+-----------+

需要这样更新：

+------+----------+-----------+-----------+-----------+
| name | location | startdate |  endate   | is_active |
+------+----------+-----------+-----------+-----------+
| A    | delhi    | 3/26/2019 | 3/28/2019 |         1 |
| A    | delhi    | 3/27/2019 | 3/27/2019 |         0 |
| A    | delhi    | 3/28/2019 | 3/28/2019 |         0 |
| A    | delhi    | 3/31/2019 | 3/31/2019 |         1 |
+------+----------+-----------+-----------+-----------+

如果 startdate 如果是连续的，则用最后一个连续的结束日期更新结束日期 startdate 以及更新 is_active = 0 连续开始日期

sql window-functions sql-server gaps-and-islands

来源：https://stackoverflow.com/questions/63156197/update-another-row-value-in-sql-server

1条答案

按热度按时间

zqdjd7g91#

这是一个缺口和孤岛问题。下面是一种使用 lag() 以及累积的 sum() 定义组。最后一步是逻辑：

select 
    name,
    location,
    startdate,
    case when row_number()  over(partition by name, location, grp order by startdate) = 1 
        then max(startdate) over(partition by name, location, grp) 
        else enddate
    end as enddate,
    case when row_number()  over(partition by name, location, grp order by startdate) = 1 
        then 1 
        else 0
    end as is_active
from (
    select 
        t.*, 
        sum(case when startdate = dateadd(day, 1, lag_enddate) then 0 else 1 end) 
            over(partition by name, location order by startdate) grp
    from (
        select 
            t.*, 
            lag(enddate) over(partition by name, location order by startdate) lag_enddate
        from mytable t
    ) t
) t

db小提琴演示：

name | location | startdate  | enddate    | is_active
:--- | :------- | :--------- | :--------- | --------:
A    | delhi    | 2019-03-26 | 2019-03-28 |         1
A    | delhi    | 2019-03-27 | 2019-03-27 |         0
A    | delhi    | 2019-03-28 | 2019-03-28 |         0
A    | delhi    | 2019-03-31 | 2019-03-31 |         1

赞(0）回复(0）举报 2021-07-27

我来回答

更新sql server中的另一行值

1条答案

相关问题

热门标签

最新问答