SQL从两表提取用户邮箱：优先@india.org.in邮箱的查询问题

问题场景与现状 #

有两张表：User（用户表）和Email（邮箱表），Email表存储每个用户的多个邮箱地址。需求是为每个用户提取一个邮箱，优先选择后缀为@india.org.in的邮箱。

当前遇到的问题：

• 例如用户1同时拥有XXXXX@india.org.in和YYYYYY@gmail.com两个邮箱，使用以下查询返回的是YYYYYY@gmail.com，不符合预期；
• 若将查询中的MAX替换为MIN，用户1能得到正确结果，但其他用户会出现错误。

当前使用的查询语句：

SELECT
    p.name,
    p.first_name,
    p.nationality,
    MAX(IF(e.email LIKE '%india.org.in%', e.email, NULL)) AS email
FROM
    User p
LEFT JOIN
    Email e ON p.name = e.parent
GROUP BY
    p.name, p.first_name, p.nationality;

问题原因 #

原查询用MAX/MIN是基于字符串字典序取值，比如YYYYYY@gmail.com的字典序可能大于XXXXX@india.org.in，所以MAX会选中它；换成MIN后，部分用户的非目标后缀邮箱字典序更小，导致错误。这种方式无法稳定保证目标后缀邮箱的优先级。

解决方案 #

方案1：使用窗口函数（推荐，兼容性好） #

通过ROW_NUMBER()窗口函数给每个用户的邮箱按优先级排序，优先保留目标后缀邮箱，再取排序第一的记录：

SELECT
    name,
    first_name,
    nationality,
    email
FROM (
    SELECT
        p.name,
        p.first_name,
        p.nationality,
        e.email,
        ROW_NUMBER() OVER (
            PARTITION BY p.name, p.first_name, p.nationality
            ORDER BY CASE WHEN e.email LIKE '%@india.org.in' THEN 0 ELSE 1 END, e.email
        ) AS rn
    FROM User p
    LEFT JOIN Email e ON p.name = e.parent
) t
WHERE rn = 1;

• PARTITION BY按用户维度分组，确保每个用户的邮箱单独排序；
• ORDER BY中的CASE语句将目标后缀邮箱优先级设为0，其他设为1，保证目标邮箱排在最前；后续的e.email用于处理多个同优先级邮箱的排序（可根据需求替换为MAX(e.email)或MIN(e.email)）；
• 外层查询取每组排序后的第一条记录，即为符合优先级要求的邮箱。

方案2：条件聚合（适用于不支持窗口函数的数据库） #

通过COALESCE先判断是否存在目标后缀邮箱，存在则优先选取，否则取其他任意邮箱：

SELECT
    p.name,
    p.first_name,
    p.nationality,
    COALESCE(
        MAX(CASE WHEN e.email LIKE '%@india.org.in' THEN e.email END),
        MAX(e.email)
    ) AS email
FROM User p
LEFT JOIN Email e ON p.name = e.parent
GROUP BY p.name, p.first_name, p.nationality;

• COALESCE会优先返回第一个非空值，即如果用户有目标后缀邮箱，就取该类邮箱的聚合值（这里用MAX，可根据需求换MIN）；
• 若用户没有目标后缀邮箱，则返回其他邮箱的聚合值。

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