MySQL查询需求：获取设备与操作组合的首次及后续执行日期

针对你要查询machine_id与operation_id唯一组合对应的首个done_on_date，以及该组合中首个日期之后的下一个日期的需求，这里提供两种实用的SQL写法，适配不同数据库环境：

方法一：用窗口函数（推荐，简洁高效） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），这是最直接的方案，用ROW_NUMBER()标记组内排序，LEAD()直接获取下一个日期：

WITH ranked_entries AS (
    SELECT
        machine_id,
        operation_id,
        done_on_date,
        -- 给每个(machine_id, operation_id)组内的记录按日期升序排号，最早的日期行号为1
        ROW_NUMBER() OVER (PARTITION BY machine_id, operation_id ORDER BY done_on_date ASC) AS rn,
        -- 获取组内当前记录的下一个日期（按排序顺序）
        LEAD(done_on_date) OVER (PARTITION BY machine_id, operation_id ORDER BY done_on_date ASC) AS next_done_on_date
    FROM maintenance_entry_table
)
SELECT
    machine_id,
    operation_id,
    done_on_date AS first_done_on_date,
    next_done_on_date
FROM ranked_entries
WHERE rn = 1; -- 筛选出每组的第一条记录（即首个日期）

逻辑说明： #

• PARTITION BY machine_id, operation_id会把数据按你需要的唯一组合分组
• ROW_NUMBER()给每组内的记录按日期从早到晚标记行号，最早的日期对应行号1
• LEAD(done_on_date)会自动抓取同一组内当前行的下一行日期，因此行号1的记录对应的next_done_on_date就是组内第二早的日期
• 最后通过WHERE rn = 1筛选出每组的首个日期及对应的下一个日期

如果你的数据库不支持CTE（公共表表达式），可以把CTE改成子查询：

SELECT
    machine_id,
    operation_id,
    done_on_date AS first_done_on_date,
    next_done_on_date
FROM (
    SELECT
        machine_id,
        operation_id,
        done_on_date,
        ROW_NUMBER() OVER (PARTITION BY machine_id, operation_id ORDER BY done_on_date ASC) AS rn,
        LEAD(done_on_date) OVER (PARTITION BY machine_id, operation_id ORDER BY done_on_date ASC) AS next_done_on_date
    FROM maintenance_entry_table
) AS ranked_entries
WHERE rn = 1;

方法二：用GROUP BY + 关联查询（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x及更早版本），可以用分组获取首个日期，再关联原表查找下一个日期：

SELECT
    t1.machine_id,
    t1.operation_id,
    t1.first_done_on_date,
    MIN(t2.done_on_date) AS next_done_on_date
FROM (
    -- 第一步：获取每个唯一组合的首个（最早）日期
    SELECT
        machine_id,
        operation_id,
        MIN(done_on_date) AS first_done_on_date
    FROM maintenance_entry_table
    GROUP BY machine_id, operation_id
) AS t1
-- 关联原表，筛选出同一组合中晚于首个日期的记录
LEFT JOIN maintenance_entry_table t2
    ON t1.machine_id = t2.machine_id
    AND t1.operation_id = t2.operation_id
    AND t2.done_on_date > t1.first_done_on_date
-- 再次分组，取晚于首个日期的最小日期（即下一个日期）
GROUP BY t1.machine_id, t1.operation_id, t1.first_done_on_date;

逻辑说明： #

1. 子查询t1通过GROUP BY和MIN(done_on_date)获取每个组合的最早日期
2. LEFT JOIN原表t2，只匹配同一组合且日期晚于首个日期的记录
3. 用MIN(t2.done_on_date)从这些匹配的记录中取最早的那个，就是紧随首个日期的下一个日期
4. 用LEFT JOIN是为了处理那些只有一条记录的组合，此时next_done_on_date会返回NULL

你之前尝试的LEFT JOIN思路是对的，只是可以优化成上面的完整写法来实现需求。

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