PostgreSQL优先匹配双字段无匹配取姓氏为空的SQL实现

• 编写单条标准SQL实现两级匹配逻辑，不依赖PL/SQL，仅使用EXISTS等基础SQL关键字
• 匹配优先级规则：
  1. 第一优先级：返回first_name、last_name均和入参完全匹配的记录
  2. 第二优先级（兜底）：如果第一优先级无匹配结果，返回first_name和入参匹配、last_name字段值为NULL的记录
• 验证场景：
  • 测试表数据：(1, 'John', NULL)、(2, 'John', 'Frank')
  • 入参为firstName=John, lastName=Jonas时，预期返回id=1
  • 入参为firstName=John, lastName=Frank时，预期返回id=2

最初写的语句无法正常运行：

1. 语法错误：WHERE子句中不支持直接使用ELSE关键字做分支判断
2. 逻辑错误：没有把「是否存在精确匹配记录」的判断和最终返回行的筛选规则正确关联
3. 细节问题：直接用SQL保留字table作为表名，会触发标识符语法报错

SELECT t.id
FROM your_table t
WHERE t.first_name = :firstName
  AND (
    -- 匹配第一优先级：名+姓完全匹配的记录
    t.last_name = :lastName
    OR
    -- 匹配第二优先级：无精确匹配时，返回姓为NULL的兜底记录
    (
      t.last_name IS NULL
      AND NOT EXISTS (
        SELECT 1
        FROM your_table t2
        WHERE t2.first_name = :firstName
          AND t2.last_name = :lastName
      )
    )
  );

• 第一层过滤先筛掉所有first_name和入参不匹配的记录，缩小判断范围
• 对名匹配的记录做分支判断：
  • 只要姓和入参完全匹配，直接纳入结果集，不受其他条件影响
  • 只有当整表不存在任何名+姓完全匹配的记录时，才会把姓为NULL的兜底记录加入结果集
• 场景验证结果完全符合预期：
  • 入参为John Frank时，存在id=2的精确匹配记录，NOT EXISTS判断结果为假，姓为NULL的id=1不会被选中，最终仅返回id=2
  • 入参为John Jonas时，不存在任何精确匹配记录，NOT EXISTS判断结果为真，此时返回姓为NULL的id=1，无其他符合条件的记录

提示：如果你的实际表名确实要使用table这类SQL保留字，查询时需要用对应数据库的标识符转义符包裹表名，例如MySQL用反引号、PostgreSQL用双引号。

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