SQL实现：优先选取PRF类型记录，无PRF时选取PRI类型记录的查询方案

你的需求核心是为每个emplid选取优先级最高的name_type记录（PRF优先，无PRF则选PRI），结合你提供的现有代码，这里有两种实用的实现方式，其中窗口函数法直观且易维护：

方法1：使用ROW_NUMBER()窗口函数（推荐） #

窗口函数可以轻松给每个emplid的记录按指定规则排序，我们只需要取排序第一的记录，就能完美匹配你的优先级需求：

WITH ranked_names AS (
    SELECT 
        a.emplid,
        a.name,
        a.last_name_srch,
        a.first_name_srch,
        a.effdt,
        -- 给name_type分配优先级：PRF排第1，PRI排第2
        ROW_NUMBER() OVER (
            PARTITION BY a.emplid 
            ORDER BY 
                CASE WHEN a.name_type = 'PRF' THEN 1 ELSE 2 END,
                a.effdt DESC  -- 同类型下取最新的effdt，和你原逻辑一致
        ) AS rn
    FROM qr_names a
)
SELECT 
    rn.emplid,
    b.national_id,
    rn.name,
    rn.last_name_srch,
    rn.first_name_srch,
    'xxxxx' || substr(b.national_id, 6, 4),
    c.birthdate
FROM ranked_names rn
JOIN qr_pers_nid b ON rn.emplid = b.emplid
JOIN qr_person c ON rn.emplid = c.emplid
WHERE rn.rn = 1  -- 只取每个emplid的第一条（优先级最高的）记录
  AND b.country = (
      SELECT max(b1.country) 
      FROM qr_pers_nid b1 
      WHERE b1.emplid = b.emplid
  );

逻辑说明： #

1. 先用CTE ranked_names 给每个emplid的qr_names记录分组排序：
   • PARTITION BY a.emplid：按emplid单独分组，每个用户的记录独立排序
   • ORDER BY CASE...：强制PRF类型的记录排在最前面，PRI类型紧随其后
   • 保留了你原逻辑里的effdt DESC，确保同类型下取最新的生效日期
2. 主查询只筛选rn=1的记录，也就是每个emplid优先级最高的那条
3. 关联其他表的逻辑和你原代码完全一致，无需额外调整

方法2：使用子查询判断PRF是否存在 #

如果你使用的数据库不支持窗口函数（比如部分老版本数据库），可以用子查询先判断每个emplid是否有PRF记录，再做筛选：

SELECT 
    a.emplid,
    b.national_id,
    a.name,
    a.last_name_srch,
    a.first_name_srch,
    'xxxxx' || substr(b.national_id, 6, 4),
    c.birthdate
FROM qr_names a
JOIN qr_pers_nid b ON a.emplid = b.emplid
JOIN qr_person c ON a.emplid = c.emplid
WHERE 
    -- 核心条件：如果有PRF则选PRF，否则选PRI
    a.name_type = CASE 
        WHEN EXISTS (SELECT 1 FROM qr_names a1 WHERE a1.emplid = a.emplid AND a1.name_type = 'PRF')
        THEN 'PRF'
        ELSE 'PRI'
    END
    AND a.effdt = (
        SELECT max(a1.effdt) 
        FROM qr_names a1 
        WHERE a1.emplid = a.emplid AND a1.name_type = a.name_type
    )
    AND b.country = (
        SELECT max(b1.country) 
        FROM qr_pers_nid b1 
        WHERE b1.emplid = b.emplid
    );

逻辑说明： #

• CASE WHEN EXISTS(...) THEN 'PRF' ELSE 'PRI'：先检查当前emplid是否存在PRF记录，存在则筛选PRF类型，否则筛选PRI类型
• 其余条件和你原代码保持一致，确保同类型下取最新的effdt

两种方法都能满足你的需求，方法1的窗口函数法更灵活——如果后续需要添加更多优先级的name_type，只需要修改ORDER BY CASE里的规则即可，维护成本更低。

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