MySQL查询需求：取最近两年数据，按Column3的A/B优先级过滤

需求回顾 #

需要从数据表中获取最近两年的数据，对每个Column4分组后按以下规则筛选：

• 若组内存在Column3='A'的行，仅返回该行
• 若不存在'A'但有Column3='B'的行，返回该行
• 其他Column3值的行直接排除

正确查询方案 #

使用窗口函数实现优先级排序和年份筛选，以下是可直接运行的SQL：

WITH recent_years AS (
    -- 给所有行按年份降序排名，获取最近的两年数据
    SELECT *,
           DENSE_RANK() OVER (ORDER BY Column2 DESC) AS year_rank
    FROM mytable
),
ranked_rows AS (
    -- 仅保留最近两年且Column3为A/B的行，按规则给每行分配组内排名
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY Column4
               ORDER BY 
                   -- 优先排序：A > B
                   CASE Column3 WHEN 'A' THEN 1 WHEN 'B' THEN 2 ELSE 3 END,
                   -- 同优先级下取年份最新的行
                   Column2 DESC,
                   -- 年份相同则取Column1最小的行（保证结果唯一）
                   Column1 ASC
           ) AS row_rank
    FROM recent_years
    WHERE year_rank <= 2
      AND Column3 IN ('A', 'B')
)
-- 取每个Column4分组的第一行（即符合优先级的行）
SELECT Column1, Column2, Column3, Column4
FROM ranked_rows
WHERE row_rank = 1
ORDER BY Column4;

方案解释 #

1. recent_years CTE：通过DENSE_RANK()给年份降序排名，确保能获取到最近的两年数据（即使年份不连续，比如最大年份是2025，次大是2023，也能正确筛选这两年）。
2. ranked_rows CTE：
   • 先过滤掉非最近两年和Column3不是A/B的行，减少计算量
   • 用ROW_NUMBER()按Column4分组，组内先按Column3的优先级排序（A优先于B），再按年份降序、Column1升序排序，保证每组只有一行符合要求的结果
3. 最终查询：筛选出每个分组的第一行，即为符合需求的结果。

验证结果 #

运行上述SQL后，将得到预期的结果集：

Column1 | Column2 | Column3 | Column4
--------|---------|---------|--------
1       | 2025    | A       | Zebra
3       | 2024    | A       | Donkey
5       | 2024    | B       | Elephant

原查询的问题说明 #

原查询仅筛选了最大年份的数据，未覆盖最近两年；同时GROUP BY Column4但直接返回非聚合列，在MySQL非严格模式下会返回每组的任意一行，无法保证按优先级筛选的逻辑正确性。

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