sql - 在 Select 语句中，根据日期间隔添加记录

Question

这是一些示例数据。选择语句结果存在差距。使用第一条记录 StopTime 和第二条记录 StartTime 来创建丢失的记录会很好吗？我该怎么做呢？

标签： sqlsql-servertsql

Answer 1

首先，考虑以下查询，它会产生缺失的记录。

select 
    StopTime StartTime,
    NextStartTime StopTime, 
    datediff(minute, StopTime, NextStartTime) DurationMin
from (
    select StopTime, lead(StartTime) over(order by StartTime) NextStartTime
    from mytable
) t
where StopTime < NextStartTime

子查询使用窗口函数检索每条记录的下一个开始时间lead()；然后，对于后跟间隙的每条记录，外部查询会生成一条记录来填充它。

然后，您可以使用union all带上您的表格，并带有一个order by用于演示的子句：

(
    select coil, StartTime, StopTime, DurationMin from mytable
    union all
    select 
        null, 
        StopTime,
        NextStartTime, 
        datediff(minute, StopTime, NextStartTime) DurationMin
    from (
        select StopTime, lead(StartTime) over(order by StartTime) NextStartTime
        from mytable
    ) t
    where StopTime < NextStartTime
)
order by StartTime

使用您的示例数据在 DB Fiddle 上进行演示：

线圈| 开始时间 | 停止时间 | DurationMin
:-------- | :------------------------ | :------------------------ | ----------:
27020-100 | 2019-09-27 10:05:00.000 | 2019-09-27 18:30:00.000 | 36
空      | 2019-09-27 18:30:00.000 | 2019-09-27 20:06:00.000 | 96
27020-100 | 2019-09-27 20:06:00.000 | 2019-09-27 20:41:00.000 | 36