在SQL中填写缺少的时间戳值

一种方法使用递归CTE生成时间列表,然后使用 left join 引入值:

with cte as (
      select min(s.time) as time, max(s.time) as maxt
      from sample s
      union all
      select dateadd(minute, 1, cte.time), cte.maxt
      from cte
      where cte.time < cte.maxt
     )
select cte.time, coalesce(s.value, 0)
from cte left join
     sample s
     on cte.time = s.time
order by cte.time;

请注意,如果您的时间超过100分钟,您将需要 option (maxrecursion 0) 在查询结束时。

你可以试着用 递归CTE 制作日历表和 OUTER JOIN 在此基础上。

CREATE TABLE T(
  [Time] Time,
  Value float
);


insert into T values ('9:00',1.2);
insert into T values ('9:01',2.3);
insert into T values ('9:05',2.4);
insert into T values ('9:06',2.5);

查询1 :

with cte as (
 SELECT MIN([Time]) minDt,MAX([Time] ) maxDt
 FROM T
 UNION ALL
 SELECT dateadd(minute, 1, minDt) ,maxDt
 FROM CTE
  WHERE dateadd(minute, 1, minDt) <= maxDt
)
SELECT t1.minDt 'Time',
       ISNULL(t2.[Value],0) 'Value'
FROM CTE t1 
LEFT JOIN T t2 on t2.[Time] = t1.minDt

Results :

|             Time | Value |
|------------------|-------|
| 09:00:00.0000000 |   1.2 |
| 09:01:00.0000000 |   2.3 |
| 09:02:00.0000000 |     0 |
| 09:03:00.0000000 |     0 |
| 09:04:00.0000000 |     0 |
| 09:05:00.0000000 |   2.4 |
| 09:06:00.0000000 |   2.5 |