sql - 更新 SQL Server 中的另一个行值

Question

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

+------+----------+-----------+-----------+-----------+
| 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连续的开始日期

标签： sqlsql-serverwindow-functionsgaps-and-islands

Answer 1

这是一个差距和孤岛问题。这是一种使用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 Fiddle 上的演示：

姓名 | 位置 | 开始日期 | 结束日期 | 活跃
:--- | :------- | :--------- | :--------- | --------:
一个 | 德里| 2019-03-26 | 2019-03-28 | 1
一个 | 德里| 2019-03-27 | 2019-03-27 | 0
一个 | 德里| 2019-03-28 | 2019-03-28 | 0
一个 | 德里| 2019-03-31 | 2019-03-31 | 1