MariaDB 10.4.24中筛选志愿者最新急救证书的SQL查询需求

原查询的问题 #

1. 逻辑运算符优先级错误：AND优先级高于OR，原语句的条件等价于(award_name = 'first aid ver1') OR (award_name = 'first aid ver2' AND volunteer_id = '123456789')，会错误包含所有ver1类型的记录，而非仅指定志愿者的两类急救证书。
2. 字段匹配问题：直接同时选择award_name和MAX(award_expiry_date)但未正确分组，会导致返回的award_name与最大过期日期不对应（MariaDB在关闭ONLY_FULL_GROUP_BY时会返回任意值）。

正确实现方法 #

方法1：使用窗口函数（推荐，MariaDB 10.2+支持）

利用ROW_NUMBER()窗口函数按志愿者分组，对急救证书按过期日期降序排序，取每组第一条（最新有效期）记录：

SELECT volunteer_id, award_name, award_expiry_date
FROM (
    SELECT 
        volunteer_id, 
        award_name, 
        award_expiry_date,
        -- 按志愿者分组，过期日期倒序编号，最新记录编号为1
        ROW_NUMBER() OVER (PARTITION BY volunteer_id ORDER BY award_expiry_date DESC) AS rn
    FROM volunteer_awards
    -- 仅筛选急救证书类型
    WHERE award_name IN ('first aid ver1', 'first aid ver2')
) AS ranked_records
-- 提取每组的最新记录
WHERE rn = 1;

如果仅需查询单个志愿者（如123456789），在子查询的WHERE后追加AND volunteer_id = '123456789'即可。

方法2：使用关联子查询

先计算每个志愿者急救证书的最大过期日期，再关联原表找到对应记录：

SELECT va.volunteer_id, va.award_name, va.award_expiry_date
FROM volunteer_awards va
-- 关联子查询得到每个志愿者的急救证书最大过期日期
JOIN (
    SELECT 
        volunteer_id, 
        MAX(award_expiry_date) AS max_expiry_date
    FROM volunteer_awards
    WHERE award_name IN ('first aid ver1', 'first aid ver2')
    GROUP BY volunteer_id
) AS max_dates 
    ON va.volunteer_id = max_dates.volunteer_id 
    AND va.award_expiry_date = max_dates.max_expiry_date
-- 再次筛选急救证书类型，避免意外匹配其他奖项
WHERE va.award_name IN ('first aid ver1', 'first aid ver2');

说明 #

两种方法都能实现需求，窗口函数更直观且性能更优（数据量较大时表现更明显）。不需要使用IF语句，核心是通过分组排序或子查询定位到每个志愿者的最新急救证书记录。

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