如何用T-SQL编写窗口查询：统计滑翔翼服务后累计飞行时长

表结构与样本数据 #

CREATE TABLE [dbo].[wings]
(
    [Id] [int] NOT NULL,
    [Manufacturer] [varchar](50) NOT NULL,
    [Model] [varchar](50) NULL,
    [Size] [decimal](3, 1) NULL,
    [hoursWhenBought] [tinyint] NULL,
    [purchaseDate] [date] NULL,

    CONSTRAINT [PK__wings__3214EC07E42B45BC] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[wingServiceHistory]
(
    [wingId] [int] NOT NULL,
    [date] [date] NOT NULL,
    [servicedBy] [varchar](100) NOT NULL,
    [comments] [varchar](200) NULL,

    CONSTRAINT [PK_wingServiceHistory] 
        PRIMARY KEY CLUSTERED ([wingId] ASC, [date] ASC)
)

CREATE TABLE [dbo].[flights]
(
    [Id] [int] NOT NULL,
    [Date] [date] NOT NULL,
    [TakeOffTime] [time](7) NOT NULL,
    [LandingTime] [time](7) NOT NULL,
    [WingId] [int] NULL
)

CREATE CLUSTERED INDEX [ClusteredIndex-Date] 
ON [dbo].[flights] ([Date] ASC)

-- Sample data
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (1, CAST(N'2019-09-02' AS Date), CAST(N'10:00:00' AS Time), CAST(N'12:00:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (2, CAST(N'2019-09-03' AS Date), CAST(N'09:30:00' AS Time), CAST(N'12:30:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (3, CAST(N'2020-05-05' AS Date), CAST(N'07:00:00' AS Time), CAST(N'08:45:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (4, CAST(N'2020-09-28' AS Date), CAST(N'13:00:00' AS Time), CAST(N'15:00:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (5, CAST(N'2021-01-03' AS Date), CAST(N'17:00:00' AS Time), CAST(N'19:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (6, CAST(N'2021-01-05' AS Date), CAST(N'15:30:00' AS Time), CAST(N'17:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (7, CAST(N'2021-08-25' AS Date), CAST(N'06:00:00' AS Time), CAST(N'08:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (8, CAST(N'2021-08-26' AS Date), CAST(N'07:00:00' AS Time), CAST(N'09:30:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (9, CAST(N'2021-09-01' AS Date), CAST(N'06:00:00' AS Time), CAST(N'07:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (10, CAST(N'2022-08-10' AS Date), CAST(N'07:00:00' AS Time), CAST(N'09:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (11, CAST(N'2022-10-17' AS Date), CAST(N'15:00:00' AS Time), CAST(N'17:00:00' AS Time), 13)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (12, CAST(N'2022-10-19' AS Date), CAST(N'16:00:00' AS Time), CAST(N'18:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (13, CAST(N'2022-12-21' AS Date), CAST(N'13:00:00' AS Time), CAST(N'15:30:00' AS Time), 13)

INSERT INTO [dbo].[wings] ([Id], [Manufacturer], [Model], [Size], [hoursWhenBought], [purchaseDate]) 
VALUES (2, N'Dudek', N'Synthesis LT', CAST(31.0 AS Decimal(3, 1)), 45, CAST(N'2017-11-04' AS Date))
INSERT INTO [dbo].[wings] ([Id], [Manufacturer], [Model], [Size], [hoursWhenBought], [purchaseDate]) 
VALUES (3, N'Dudek', N'Universal 1.1', CAST(28.0 AS Decimal(3, 1)), 0, CAST(N'2019-08-23' AS Date))
INSERT INTO [dbo].[wings] ([Id], [Manufacturer], [Model], [Size], [hoursWhenBought], [purchaseDate]) 
VALUES (8, N'Dudek', N'Nucleon XX', CAST(24.0 AS Decimal(3, 1)), 150, CAST(N'2021-01-02' AS Date))
INSERT INTO [dbo].[wings] ([Id], [Manufacturer], [Model], [Size], [hoursWhenBought], [purchaseDate]) 
VALUES (13, N'Dudek', N'Hadron 3', CAST(20.0 AS Decimal(3, 1)), 3, CAST(N'2022-10-16' AS Date))

INSERT INTO [dbo].[wingServiceHistory] ([wingId], [date], [servicedBy], [comments]) 
VALUES (3, CAST(N'2020-09-21' AS Date), N'Joe Blogs', N'full trim service')
INSERT INTO [dbo].[wingServiceHistory] ([wingId], [date], [servicedBy], [comments]) 
VALUES (8, CAST(N'2021-08-24' AS Date), N'Joe Blogs', N'full trim service')
INSERT INTO [dbo].[wingServiceHistory] ([wingId], [date], [servicedBy], [comments]) 
VALUES (8, CAST(N'2022-08-03' AS Date), N'Joe Blogs', N'full trim service')

现有查询 #

已有查询可返回所有滑翔翼的飞行时长及累计飞行时长：

SELECT
    Id,
    [Date],
    CAST(DATEADD(minute, DATEDIFF(minute, [TakeOffTime], [LandingTime]), 0) AS time) AS Duration,
    CAST ((SUM(DATEDIFF(minute, [TakeOffTime], [LandingTime])) OVER (ORDER BY [Id]) / 60.0) AS DECIMAL(10, 1)) AS CumulativeHours
FROM  
    flights

查询需求 #

需编写T-SQL查询，按flights.Id排序，对flights表中的每个航班，计算对应WingId自**上次服务日期（来自wingServiceHistory表）或购买日期（来自wings表，取两者中较晚的日期）**起的累计飞行时长，需包含购买时已有的飞行小时数。

期望结果集包含flights表的所有列，加上duration、cumulativeHours、cumulativeHoursSinceService列。

示例结果 #

内容的提问来源于stack exchange，提问作者Mark Allison