如何使用SQL判断Serial Number出现次数并生成days_since_last_fix字段

咱们先梳理下你原SQL里的几个问题，然后给你一个高效的正确方案：

• 用笛卡尔积（FROM table t1, table t2）会生成大量重复数据，同一设备的n条维修记录会产出n*(n-1)行，不符合你要和原表行数一致的需求
• CASE逻辑有偏差：第二个条件t1.serial_number = t1.serial_number永远为真，会覆盖前面的判断；而且需求里明确单次出现要赋值0，不是计算生产日和维修日的差值
• 函数名不一致（datediff和date_diff），不同SQL方言的日期差语法有差异，得统一处理

核心实现思路 #

用**窗口函数LEAD()**是最适合的方案，它能在同一设备的分组内直接获取下一次维修的日期，完美匹配你的需求：

1. 按serial_number分组，把同一设备的所有维修记录归到一起
2. 对每个分组内的记录按date_fixed（或order_number，保证维修顺序正确）排序
3. 用LEAD()抓取当前行的下一次维修日期
4. 根据是否存在下一次维修日期，计算间隔天数或直接赋值0

分方言的具体SQL代码 #

你可以根据自己使用的数据库选择对应的版本：

MySQL 8.0+ / MariaDB

SELECT
    serial_number,
    produced_date,
    date_fixed,
    days_since_fixed,
    order_number,
    CASE
        -- 存在下一次维修时，计算日期差
        WHEN LEAD(date_fixed) OVER (PARTITION BY serial_number ORDER BY date_fixed) IS NOT NULL
            THEN DATEDIFF(LEAD(date_fixed) OVER (PARTITION BY serial_number ORDER BY date_fixed), date_fixed)
        -- 无下一次维修（单次记录或分组最后一行），赋值0
        ELSE 0
    END AS days_since_last_fix
FROM your_table_name;

PostgreSQL

SELECT
    serial_number,
    produced_date,
    date_fixed,
    days_since_fixed,
    order_number,
    CASE
        WHEN LEAD(date_fixed) OVER (PARTITION BY serial_number ORDER BY date_fixed) IS NOT NULL
            THEN (LEAD(date_fixed) OVER (PARTITION BY serial_number ORDER BY date_fixed) - date_fixed)::INT
        ELSE 0
    END AS days_since_last_fix
FROM your_table_name;

BigQuery

SELECT
    serial_number,
    produced_date,
    date_fixed,
    days_since_fixed,
    order_number,
    CASE
        WHEN LEAD(date_fixed) OVER (PARTITION BY serial_number ORDER BY date_fixed) IS NOT NULL
            THEN DATE_DIFF(LEAD(date_fixed) OVER (PARTITION BY serial_number ORDER BY date_fixed), date_fixed, DAY)
        ELSE 0
    END AS days_since_last_fix
FROM your_table_name;

测试你的示例数据 #

用你给出的测试数据运行上述MySQL版本的SQL，输出结果会是：

完全符合你的需求：单次维修的设备days_since_last_fix为0，多次维修的第一条记录计算出与下一次维修的间隔（2天），最后一条维修记录赋值0。

兼容旧版本数据库的方案 #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用关联子查询实现（适合小数据量）：

SELECT
    t1.*,
    COALESCE(
        (SELECT DATEDIFF(t2.date_fixed, t1.date_fixed)
         FROM your_table_name t2
         WHERE t2.serial_number = t1.serial_number
           AND t2.date_fixed > t1.date_fixed
         ORDER BY t2.date_fixed ASC LIMIT 1),
        0
    ) AS days_since_last_fix
FROM your_table_name t1
ORDER BY t1.serial_number, t1.date_fixed;

内容的提问来源于stack exchange，提问作者Ray Syu