SQL多行值统计：查询指定年份车辆连续未使用月份数

嘿，我来帮你搞定这个连续未使用月份的统计需求！首先咱们先明确下场景：咱们有一张vehicle_usage表，里面按年月记录了车辆的使用状态（1=在用，0=闲置），需要针对指定年份，统计从「截止月份」向前倒推的连续闲置月份数，遇到在用记录就停止统计。

先给你明确两个核心场景的规则：

• 场景1：查询当前未结束的年份（比如现在是2018年2月，查year=2018）：从当前月份（2018-02）开始往前倒推，数连续的0，直到碰到1为止，示例结果是3（对应2018-02、2017-12、2017-11这三个月都是0）
• 场景2：查询已结束的年份（比如查year=2017）：直接从该年份的12月往前倒推，统计连续0的数量，你提到原示例的3是错误的，正确结果应该是1，咱们按这个逻辑来。

下面给你几个不同版本的SQL方案，适配不同的MySQL版本：

方案1：用窗口函数（MySQL 8.0+ 推荐） #

这个方案逻辑清晰，兼容性好，适合新版本MySQL：

-- 先设置目标年份，替换成你要查询的年份即可
SET @target_year = 2018;

WITH ordered_months AS (
    -- 筛选出所有需要检查的年月，按时间从近到远排序
    SELECT 
        year,
        month,
        status,
        -- 把年月转成日期格式，方便排序和比较
        STR_TO_DATE(CONCAT(year, '-', month, '-01'), '%Y-%m-%d') AS month_date
    FROM vehicle_usage
    WHERE STR_TO_DATE(CONCAT(year, '-', month, '-01'), '%Y-%m-%d') <= 
        CASE 
            -- 如果是当前年份，用当前月份作为截止点
            WHEN YEAR(CURDATE()) = @target_year THEN CURDATE()
            -- 如果是已结束的年份，用当年12月作为截止点
            ELSE STR_TO_DATE(CONCAT(@target_year, '-12-01'), '%Y-%m-%d')
        END
    ORDER BY month_date DESC
),
continuous_check AS (
    SELECT 
        *,
        -- 用窗口函数累加遇到的在用记录，一旦碰到1，后续的flag都会变成1
        SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) OVER (ORDER BY month_date DESC) AS has_used_flag
    FROM ordered_months
)
-- 统计flag为0的记录数，就是连续闲置的月份数
SELECT COUNT(*) AS continuous_unused_months
FROM continuous_check
WHERE has_used_flag = 0;

逻辑解释： #

1. ordered_months：把所有需要检查的年月按时间从近到远排好队，自动区分当前年份和已结束年份的截止点。
2. continuous_check：用窗口函数给每条记录打标记，只要前面（时间更近的）出现过1，这条记录的has_used_flag就会变成1，没出现过的就是0。
3. 最后统计has_used_flag=0的记录数，就是咱们要的连续闲置月份数。

方案2：递归CTE（MySQL 8.0+） #

如果喜欢递归的思路，这个方案更直观，像一步步往前翻日历：

SET @target_year = 2017;
-- 自动确定起始月份：当前年份用当前月，已结束年份用12月
SET @start_month = CASE WHEN YEAR(CURDATE()) = @target_year THEN MONTH(CURDATE()) ELSE 12 END;

WITH RECURSIVE month_sequence AS (
    -- 起始节点：从目标年份的起始月份开始
    SELECT 
        @target_year AS year,
        @start_month AS month,
        (SELECT status FROM vehicle_usage WHERE year = @target_year AND month = @start_month) AS status
    UNION ALL
    -- 递归生成上一个月的记录
    SELECT 
        CASE WHEN month = 1 THEN year - 1 ELSE year END AS year,
        CASE WHEN month = 1 THEN 12 ELSE month - 1 END AS month,
        (SELECT status FROM vehicle_usage WHERE year = CASE WHEN month = 1 THEN year - 1 ELSE year END AND month = CASE WHEN month = 1 THEN 12 ELSE month - 1 END) AS status
    FROM month_sequence
    -- 递归终止条件：只有当前记录是闲置（0）才继续往前查
    WHERE status = 0
)
-- 统计所有闲置的记录数
SELECT COUNT(*) AS continuous_unused_months
FROM month_sequence
WHERE status = 0;

逻辑解释： #

从起始月份开始，每次生成上一个月的记录，只要当前月份是闲置状态就继续递归，直到碰到在用状态或者没有更早的记录为止，最后数一下所有闲置的记录就行。

方案3：变量实现（兼容MySQL 5.x） #

如果你的MySQL版本低于8.0，不支持窗口函数和递归，就用这个变量方案：

SET @target_year = 2018;
SET @stop_flag = 0;
SET @count = 0;

SELECT 
    SUM(CASE WHEN @stop_flag = 0 AND status = 0 THEN 1 ELSE 0 END) AS continuous_unused_months
FROM (
    -- 按时间从近到远排序所有需要检查的年月
    SELECT 
        year,
        month,
        status
    FROM vehicle_usage
    WHERE STR_TO_DATE(CONCAT(year, '-', month, '-01'), '%Y-%m-%d') <= 
        CASE 
            WHEN YEAR(CURDATE()) = @target_year THEN CURDATE()
            ELSE STR_TO_DATE(CONCAT(@target_year, '-12-01'), '%Y-%m-%d')
        END
    ORDER BY STR_TO_DATE(CONCAT(year, '-', month, '-01'), '%Y-%m-%d') DESC
) AS ordered_list
-- 一旦碰到在用记录，就把stop_flag设为1，后续不再统计
WHERE (@stop_flag := CASE WHEN status = 1 THEN 1 ELSE @stop_flag END) = 0;

逻辑解释： #

用变量@stop_flag控制是否停止统计，按时间倒序遍历，碰到1就把stop_flag设为1，之后的记录都不再计入统计，最后累加符合条件的闲置月份数。

注意事项： #

• 确保vehicle_usage表中每个年月都有记录，如果存在缺失的年月，需要根据业务逻辑处理（比如用COALESCE(status, 0)把缺失记录视为闲置，或者调整查询逻辑）。
• 替换@target_year为你实际要查询的年份即可，无需修改其他逻辑。

内容的提问来源于stack exchange，提问作者ASP YOK