MySQL查询需求：同一ID内生成End Date列的实现方案

Got it, let's work through this MySQL query problem to get the exact result you need. First, I'll make a few reasonable assumptions since your question didn't specify exact table/column names—feel free to adjust them to match your actual schema:

• Let's say your table is named status_records
• The status column (which has the 'Resolved' value) is called record_status
• The date column is actual_create_date (using underscores to follow MySQL naming conventions)

Here's the query that implements your End Date logic perfectly:

SELECT
    id,
    data,
    actual_create_date,
    record_status,
    CASE
        WHEN record_status = 'Resolved' THEN actual_create_date
        ELSE LEAD(actual_create_date) OVER (PARTITION BY id ORDER BY actual_create_date)
    END AS end_date
FROM status_records;

How This Works #

Let's break down the key parts:

1. LEAD() Window Function: This is the core of getting the next row's date.
   • PARTITION BY id ensures we only look at rows belonging to the same ID group.
   • ORDER BY actual_create_date makes sure we pull the chronologically next record's creation date (so we don't mix up row order).
2. CASE Statement: Handles the two rules you specified:
   • If the record is marked 'Resolved', end_date matches its own actual_create_date.
   • For non-Resolved records, we use the next row's date from the LEAD() function.

Optional Adjustment for Last Rows #

If a non-Resolved record is the final entry for its ID, LEAD() will return NULL for end_date. If you want to handle this (e.g., set it to the current date or a placeholder), use COALESCE to add a fallback:

SELECT
    id,
    data,
    actual_create_date,
    record_status,
    CASE
        WHEN record_status = 'Resolved' THEN actual_create_date
        ELSE COALESCE(LEAD(actual_create_date) OVER (PARTITION BY id ORDER BY actual_create_date), CURRENT_TIMESTAMP())
    END AS end_date
FROM status_records;

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