如何在SQL Server中实现飞行部件更换状态的复杂查询？

我需要针对轻型飞机飞行跟踪数据库编写SQL查询，要求返回MotorParts表的全部列，同时新增两个计算列：

• HoursSinceReplacement：部件上次更换后的累计飞行小时数；若该部件无更换记录，则取对应电机的总飞行小时数
• Status：当HoursSinceReplacement超过该部件的ReplaceHours阈值时显示Replace，否则显示OK

涉及的数据库对象包括：Motors、MotorParts、MotorPartServiceHistory、Flights表，以及FlightDetails视图。

以下是实现需求的查询语句，通过CTE分步处理总飞行小时和最近更换记录，确保逻辑清晰：

-- 计算每个电机的总飞行小时（兼容无飞行记录的电机）
WITH MotorTotalFlightHours AS (
    SELECT
        m.MotorID,
        COALESCE(SUM(fd.FlightHours), 0) AS TotalHours
    FROM Motors m
    LEFT JOIN FlightDetails fd ON m.MotorID = fd.MotorID
    GROUP BY m.MotorID
),
-- 获取每个部件的最近更换记录及更换时的累计飞行小时
LatestPartService AS (
    SELECT
        mpsh.MotorPartID,
        MAX(mpsh.CumulativeFlightHours) AS HoursAtLastReplacement
    FROM MotorPartServiceHistory mpsh
    GROUP BY mpsh.MotorPartID
)
SELECT
    mp.*,
    -- 计算上次更换后的飞行小时：有更换记录则用总小时减更换时的小时，无则直接取总小时
    COALESCE(mtfh.TotalHours - lps.HoursAtLastReplacement, mtfh.TotalHours) AS HoursSinceReplacement,
    -- 判断部件状态
    CASE
        WHEN COALESCE(mtfh.TotalHours - lps.HoursAtLastReplacement, mtfh.TotalHours) > mp.ReplaceHours
        THEN 'Replace'
        ELSE 'OK'
    END AS Status
FROM MotorParts mp
JOIN MotorTotalFlightHours mtfh ON mp.MotorID = mtfh.MotorID
LEFT JOIN LatestPartService lps ON mp.MotorPartID = lps.MotorPartID;

1. 电机总飞行小时计算：通过MotorTotalFlightHours CTE关联Motors和FlightDetails，聚合得到每个电机的总飞行时长，用COALESCE处理无飞行记录的电机，避免空值干扰后续计算。
2. 最近更换记录获取：LatestPartService CTE通过MAX函数筛选每个部件的最近更换记录（假设CumulativeFlightHours字段存储了更换时电机的累计飞行小时）。
3. HoursSinceReplacement计算：使用COALESCE判断是否存在更换记录，有则用总小时减去更换时的小时，无则直接取总小时。
4. 状态判断：通过CASE语句对比计算出的时长和部件的更换阈值，输出对应的状态标识。

• 如果MotorPartServiceHistory未直接存储更换时的累计飞行小时，需要调整LatestPartService的逻辑：比如根据更换日期，计算该日期前的电机总飞行小时数。
• 若FlightDetails视图的飞行时长统计逻辑特殊（比如包含重复记录），需同步调整MotorTotalFlightHours中的聚合方式，确保总小时数准确。

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