sql-server - T-SQL - 每分钟检查日期时间数据中的差距

Question

我们有一个数据库，每分钟记录一次来自系统的数据

+---------+-------------------------+  
| Id      | EntryDate               |     
+---------+-------------------------+   
| 8093562 | 2019-05-17 15:01:25.000 |  
| 8093563 | 2019-05-17 15:02:25.000 |  
| 8093564 | 2019-05-17 15:03:25.000 |  
| 8093565 | 2019-05-17 15:04:25.000 |  
| 8093566 | 2019-05-17 15:05:25.000 |  
| 8093568 | 2019-05-17 15:07:25.000 |  
| 8093569 | 2019-05-17 15:08:25.000 |  
| 8093780 | 2019-05-17 15:09:25.000 |  
| 8093781 | 2019-05-17 15:10:25.000 |  
+---------+-------------------------+   

我正在尝试进行查询，以查找分钟之间是否存在任何差距。例如在上面的数据中，15:06 的数据丢失了。

无法弄清楚如何在 SQL 查询中执行此操作。

Answer 1

将LEAD()与CASE结合使用，您可以获得预期的结果:

SELECT ResultDate FROM (
    SELECT CASE WHEN (DATEDIFF(MINUTE, LEAD(EntryDate, 1) OVER(ORDER BY EntryDate),EntryDate) < -1) THEN DATEADD(MINUTE, +1, EntryDate) END AS ResultDate
    FROM TableName
) Q WHERE ResultDate IS NOT NULL

给定示例数据的演示:

DECLARE  @TestTable TABLE (Id INT, EntryDate DATETIME);

INSERT INTO @TestTable (Id, EntryDate) VALUES  
(8093562, '2019-05-17 15:01:25.000'),
(8093563, '2019-05-17 15:02:25.000'),
(8093564, '2019-05-17 15:03:25.000'),
(8093565, '2019-05-17 15:04:25.000'),
(8093566, '2019-05-17 15:05:25.000'),
(8093568, '2019-05-17 15:07:25.000'),
(8093569, '2019-05-17 15:08:25.000'),
(8093780, '2019-05-17 15:09:25.000'),
(8093781, '2019-05-17 15:10:25.000');

SELECT ResultDate FROM (
    SELECT CASE WHEN (DATEDIFF(MINUTE, LEAD(EntryDate, 1) OVER(ORDER BY EntryDate),EntryDate) < -1) THEN DATEADD(MINUTE, +1, EntryDate) END AS ResultDate
    FROM @TestTable
) Q WHERE ResultDate IS NOT NULL

输出:

ResultDate
-----------------------
2019-05-17 15:06:25.000

Demo on db<>fiddle