MySQL查询：筛选满足条件的最大rev_number记录

现有MySQL表结构及数据如下：

需求：

• 筛选出每个gen_loc对应的最大rev_number的记录
• 仅保留那些最大rev_number对应的记录中存在action != DELETE且status = PROCESSED的gen_loc
• 若某个gen_loc的最大rev_number对应的所有记录都是DELETE（比如10A6的rev10），则不显示该gen_loc的任何记录

此前尝试用MAX(rev_number)查询时，得到的是rev_number为09的记录，期望得到的结果如下：

请问如何编写符合要求的MySQL查询语句？

方法一：子查询关联（兼容所有MySQL版本） #

通过子查询先筛选出符合条件的gen_loc及其最大版本号，再关联原表获取具体有效记录：

SELECT t.gen_loc, t.rev_number, t.status, t.action
FROM your_table t
JOIN (
    SELECT 
        gen_loc, 
        MAX(rev_number) AS max_rev
    FROM your_table
    GROUP BY gen_loc
    HAVING SUM(CASE WHEN rev_number = MAX(rev_number) AND action != 'DELETE' AND status = 'PROCESSED' THEN 1 ELSE 0 END) > 0
) AS valid_locs ON t.gen_loc = valid_locs.gen_loc AND t.rev_number = valid_locs.max_rev
WHERE t.action != 'DELETE' AND t.status = 'PROCESSED';

逻辑说明：

1. 内层子查询按gen_loc分组，计算每个分组的最大rev_number；同时通过HAVING子句统计该最大版本下有效记录（action≠DELETE且status=PROCESSED）的数量，仅保留数量大于0的分组。
2. 外层查询将原表与子查询结果关联，筛选出对应gen_loc和最大版本号的记录，并且只保留自身为有效记录的行。

方法二：窗口函数（MySQL 8.0及以上版本支持） #

利用窗口函数简化逻辑，一次性计算所需的最大版本号和有效记录数：

WITH ranked_data AS (
    SELECT 
        *,
        MAX(rev_number) OVER (PARTITION BY gen_loc) AS max_rev,
        COUNT(CASE WHEN action != 'DELETE' AND status = 'PROCESSED' THEN 1 END) OVER (PARTITION BY gen_loc, rev_number) AS valid_count
    FROM your_table
)
SELECT gen_loc, rev_number, status, action
FROM ranked_data
WHERE rev_number = max_rev AND valid_count > 0 AND action != 'DELETE' AND status = 'PROCESSED';

逻辑说明：

1. 用MAX(rev_number) OVER (PARTITION BY gen_loc)计算每个gen_loc的最大版本号。
2. 用COUNT(CASE...) OVER (PARTITION BY gen_loc, rev_number)统计每个gen_loc对应版本下的有效记录数。
3. 最后筛选出版本号等于最大版本、有效记录数大于0，且自身为有效记录的行。

注：将上述语句中的your_table替换为实际的表名即可。

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