如何按指定条件从分组中选单条记录：学生家庭联系人筛选

你可以借助窗口函数ROW_NUMBER()实现需求，核心思路是给每位学生的联系人按优先级排序后取首位，同时调整连接方式保留无联系人的学生记录，参考实现如下：

WITH ranked_contacts AS (
    SELECT 
        s.studentnr,
        pc.pupilid,
        pc.contactpupilid,
        p2.mainmail,
        ROW_NUMBER() OVER (
            PARTITION BY s.studentnr 
            ORDER BY 
                -- 优先级1：有邮箱的联系人排在最前
                CASE WHEN p2.mainmail IS NOT NULL THEN 1 ELSE 2 END,
                -- 优先级2：同优先级下取最早添加的第一位联系人，可按需替换为其他排序字段
                pc.id
        ) AS rn
    FROM student s
    LEFT JOIN pupil p ON p.id = s.pupilid
    LEFT JOIN pupilcontact pc 
        ON pc.pupilid = p.id 
        AND CURRENT_DATE BETWEEN pc.validfrom AND pc.validuntil
    LEFT JOIN pupil p2 ON p2.id = pc.contactpupilid
)
SELECT studentnr, pupilid, contactpupilid, mainmail
FROM ranked_contacts
-- 取每个学生排序第一的联系人，同时保留无任何联系人的学生
WHERE rn = 1 OR rn IS NULL
ORDER BY studentnr;

逻辑说明 #

• 分组排序：用PARTITION BY s.studentnr按学生拆分联系人列表，优先给有邮箱的联系人打更低的排序值，保证优先被选中
• 兼容无联系人场景：将原SQL的内连接改为左连接，同时把联系人有效期校验移到JOIN条件中，避免过滤掉没有有效联系人的学生
• 无邮箱场景适配：所有联系人都无邮箱时，会按pc.id（也就是联系人添加的先后顺序）取第一位联系人，你也可以根据业务需求替换为其他排序规则，比如监护人优先级等。

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