MySQL查询：获取名称最后变更后的START/STOP状态记录

Absolutely, you can pull those records with a single SQL query—no need for multiple separate queries. Let's walk through how to make this work for your specific case.

Core Approach #

Your goal is to get all START/STOP entries for ABC that happen after the last time the name switched from something else (like XYZ to ABC). The key steps are:

• Find the most recent record where the name isn't ABC (this marks the end of the prior name group).
• Fetch all ABC records that come after that point.

Query for Your Exact Case #

Here's a straightforward query that returns exactly the rows you're expecting:

SELECT id, name, Status
FROM your_table
WHERE id > (
    -- Get the largest ID of any non-ABC record
    SELECT MAX(id)
    FROM your_table
    WHERE name != 'ABC'
)
AND name = 'ABC';

In your sample data, this will return rows 13, 14, 17, 18, 21, and 22—perfect match for your desired output.

More Flexible Version (For Any Last Name Change) #

If you need a query that works regardless of what the final name is (e.g., if the last switch was to XYZ instead of ABC), use this adaptable version:

WITH name_change_points AS (
    SELECT
        id,
        name,
        -- Compare current row's name to the previous row's name
        LAG(name) OVER (ORDER BY id) AS prior_name
    FROM your_table
),
last_change AS (
    SELECT MAX(id) AS last_switch_id
    FROM name_change_points
    WHERE name != prior_name
)
SELECT id, name, Status
FROM your_table
WHERE id > (SELECT last_switch_id FROM last_change)
AND name = (SELECT name FROM your_table ORDER BY id DESC LIMIT 1);

This uses the LAG() window function to detect every point where the name changed between consecutive rows (even if IDs have gaps). It then grabs the last of those change points and fetches all records matching the most recent name in the table.

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