时间戳

首页 > 解决方案 > SQL Server：获取同一表的日期值之间的实际最小值

sql - SQL Server：获取同一表的日期值之间的实际最小值

问题描述

我有下表存储每分钟的 gps 移动点。

CREATE TABLE [POINTS]
(
    [ID_POINTS] [bigint] IDENTITY(1,1) NOT NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [PointDate] [datetime2](7) NULL,
    [Activity] [nvarchar](100) NULL,
    [ID_WORKER] [int] NULL,
    [ID_DEVICE] [int] NULL, 
    [Control] [nvarchar](10) NULL,

    CONSTRAINT [PK_POINTS] 
        PRIMARY KEY CLUSTERED ([ID_POINTS] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON)
)

请参阅显示以下列的示例数据id_worker：activity和pointdate：

1   DRAWING 2021-07-31 11:59:38.7510000
1   DRAWING 2021-07-31 14:29:01.5680000
1   DRAWING 2021-07-31 14:30:06.1930000
1   DRAWING 2021-07-31 14:31:07.1960000
1   DRAWING 2021-07-31 14:32:13.1960000
1   DRAWING 2021-07-31 14:33:19.1930000
1   DRAWING 2021-07-31 14:34:21.1800000
1   DRAWING 2021-07-31 14:35:23.1720000
1   DRAWING 2021-07-31 14:36:24.1730000
1   DRAWING 2021-07-31 14:37:24.2190000
1   DRAWING 2021-07-31 14:38:29.1790000
1   DRAWING 2021-07-31 14:39:31.1720000
1   DRAWING 2021-07-31 14:40:34.1620000
1   DRAWING 2021-07-31 14:53:11.1670000
1   DRAWING 2021-07-31 14:54:12.2140000
1   DRAWING 2021-07-31 14:55:12.2140000
1   DRAWING 2021-07-31 14:56:14.1710000
1   DRAWING 2021-07-31 14:57:18.2150000
1   DRAWING 2021-07-31 14:58:24.1740000
1   DRAWING 2021-07-31 14:59:29.1970000
1   DRAWING 2021-07-31 15:00:31.1780000
1   DRAWING 2021-07-31 15:01:36.2190000
1   DRAWING 2021-07-31 15:02:36.2240000
1   DRAWING 2021-07-31 15:03:42.2000000
1   DRAWING 2021-07-31 15:04:46.1840000
1   DRAWING 2021-07-31 15:05:50.1640000
1   DRAWING 2021-07-31 15:06:54.2090000
1   DRAWING 2021-07-31 15:07:54.2380000
1   DRAWING 2021-07-31 15:08:54.2380000
1   DRAWING 2021-07-31 15:10:00.1910000
1   DRAWING 2021-07-31 15:11:02.1810000
1   DRAWING 2021-07-31 15:12:06.2220000
1   DRAWING 2021-07-31 15:13:12.2100000
1   DRAWING 2021-07-31 15:14:12.2180000
1   DRAWING 2021-07-31 15:15:18.2160000
1   DRAWING 2021-07-31 15:16:18.2180000
1   DRAWING 2021-07-31 15:17:18.2180000
1   DRAWING 2021-07-31 15:18:18.2220000
1   DRAWING 2021-07-31 15:19:18.2220000
1   DRAWING 2021-07-31 15:20:24.2120000
1   DRAWING 2021-07-31 15:21:30.2240000
1   DRAWING 2021-07-31 15:22:36.2140000
1   DRAWING 2021-07-31 15:23:42.2180000
1   DRAWING 2021-07-31 15:24:48.2150000
1   DRAWING 2021-07-31 15:25:54.1810000
1   DRAWING 2021-07-31 15:26:54.2140000
1   DRAWING 2021-07-31 15:27:54.2360000
1   DRAWING 2021-07-31 15:29:00.2350000
1   DRAWING 2021-07-31 15:30:06.2200000

我需要得到不包括午餐时间的总分钟数，从 10:00 到 10:30 以及从 14:00 到 15:00

期望的结果必须是 2 小时 30 分钟

标签： sqlsql-serverazure

解决方案

使用 SQL Server 的 LEAD 函数，我可以访问下一行以根据所有行的 PointDate 字段计算持续时间

推荐阅读