编写MySQL查询：筛选每月仅含expired状态的序列号记录

Got it, let's tackle this problem. You want to pull records where, for each group (either grouped by sr_number + the month of end_time, or sr_number + full end_time date), every entry in that group has a status of 'expired'—no other statuses allowed in the group. Here's how to make that happen:

Step-by-Step Approach #

The core idea is to first identify which groups (sr_number + time dimension) meet the "only expired" rule, then use those valid groups to filter the original table and get the matching records.

1. Group by Month of end_time #

If your grouping needs to be at the year-month level (e.g., 2017-01), use this query:

SELECT t.*
FROM test t
JOIN (
    -- First, filter groups where only 'expired' exists
    SELECT
        sr_number,
        DATE_FORMAT(end_time, '%Y-%m') AS end_month
    FROM test
    GROUP BY sr_number, DATE_FORMAT(end_time, '%Y-%m')
    HAVING
        COUNT(DISTINCT status) = 1 -- Ensure only one unique status in the group
        AND MAX(status) = 'expired' -- Confirm that status is 'expired'
) valid_groups
    ON t.sr_number = valid_groups.sr_number
    AND DATE_FORMAT(t.end_time, '%Y-%m') = valid_groups.end_month;

2. Group by Full end_time Date #

If you need to group by the complete end_time date (e.g., 2017-01-15), adjust the grouping fields like this:

SELECT t.*
FROM test t
JOIN (
    SELECT sr_number, end_time
    FROM test
    GROUP BY sr_number, end_time
    HAVING
        COUNT(DISTINCT status) = 1
        AND MAX(status) = 'expired'
) valid_groups
    ON t.sr_number = valid_groups.sr_number
    AND t.end_time = valid_groups.end_time;

Note on Your Sample Data #

Looking at the sample data you shared, none of the groups actually meet the "only expired" condition:

• For s1, the 2017-01-15 group includes both purchase and expired statuses
• s2 has groups with purchase/expired and renew
• s3 has groups with purchase and extends

That means the above queries would return no results for your sample data. If you expected s1's expired record to be returned, there might be a misalignment in the requirement description or sample data—for example, if s1's two records had different end_time months, the group with only expired would be picked up.

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