在SQL Server中如何基于累计运行年限计算年度运行年限？

原始车辆运行数据 #

在SQL Server中维护的车辆运行数据如下：

期望统计结果 #

需要基于上述数据计算年度运行年限，得到如下结果：

统计规则 #

• 年份列的起始年份为启动日期中的最小年份
• 年份列的结束年份为故障日期中的最大年份

SQL实现代码 #

-- 生成连续年份序列并统计年度运行车辆数
WITH YearRange AS (
    -- 获取年份范围的起止值
    SELECT MIN(YEAR(启动日期)) AS StartYear, MAX(YEAR(故障日期)) AS EndYear
    FROM 车辆运行表
),
Years AS (
    -- 递归生成连续年份列表
    SELECT StartYear AS [年份]
    FROM YearRange
    UNION ALL
    SELECT [年份] + 1
    FROM Years
    WHERE [年份] < (SELECT EndYear FROM YearRange)
)
-- 关联原始数据统计每个年份的运行车辆数
SELECT 
    y.[年份],
    COUNT(DISTINCT v.Vehicle_ID) AS [年度运行年限]
FROM Years y
LEFT JOIN 车辆运行表 v
    ON y.[年份] BETWEEN YEAR(v.启动日期) AND YEAR(v.故障日期)
GROUP BY y.[年份]
ORDER BY y.[年份];

代码说明 #

1. YearRange CTE：计算所有车辆启动日期的最小年份和故障日期的最大年份，确定统计的时间范围。
2. Years CTE：通过递归方式生成从起始到结束的连续年份列表，确保年份无缺失。
3. 主查询：将年份列表与原始车辆数据关联，判断每个年份是否在车辆的运行周期内，统计每个年份的不同运行车辆数量，即为年度运行年限。

内容的提问来源于stack exchange，提问作者Mr.RK