如何编写优先匹配pin列、无匹配时Fallback到姓名的SQL条件查询？

你的原查询问题在于用OR逻辑会同时返回pin精确匹配和姓名前缀匹配的结果，无法满足“pin匹配存在时就不返回姓名匹配记录”的优先级要求。下面提供两种高效的解决方案：

方案一：用UNION ALL + NOT EXISTS分情况查询 #

这种方式逻辑直观，先检查是否存在pin匹配的记录，存在则只返回这些记录；不存在则返回姓名前缀匹配的记录：

-- 优先返回pin精确匹配的记录
SELECT delegateId, firstName, lastName
FROM delegate
WHERE eventId = 1 AND pin = 'Jones'

UNION ALL

-- 仅当没有pin匹配时，返回姓名前缀匹配的记录
SELECT delegateId, firstName, lastName
FROM delegate
WHERE eventId = 1 
  AND (firstName LIKE 'Jones%' OR lastName LIKE 'Jones%')
  AND NOT EXISTS (
    SELECT 1 
    FROM delegate 
    WHERE eventId = 1 AND pin = 'Jones'
  );

逻辑说明 #

1. 第一个查询直接筛选pin = 'Jones'的记录（因为pin唯一，最多返回1条）；
2. 第二个查询的NOT EXISTS条件会判断是否存在pin匹配的记录：
   • 如果存在，第二个查询返回空；
   • 如果不存在，才返回姓名前缀匹配的记录。

方案二：用窗口函数标记优先级筛选 #

通过给不同匹配类型标记优先级，最终只取最高优先级的分组：

SELECT delegateId, firstName, lastName
FROM (
  SELECT 
    delegateId, firstName, lastName,
    -- 给pin匹配的记录标记最高优先级（1），姓名匹配标记次优先级（2）
    CASE WHEN pin = 'Jones' THEN 1 ELSE 2 END AS priority
  FROM delegate
  WHERE eventId = 1
    AND (pin = 'Jones' OR firstName LIKE 'Jones%' OR lastName LIKE 'Jones%')
) AS ranked_records
-- 只取优先级最高的分组（如果有pin匹配则取1，否则取2）
WHERE priority = (
  SELECT MIN(priority)
  FROM (
    SELECT CASE WHEN pin = 'Jones' THEN 1 ELSE 2 END AS priority
    FROM delegate
    WHERE eventId = 1
      AND (pin = 'Jones' OR firstName LIKE 'Jones%' OR lastName LIKE 'Jones%')
  ) AS priority_check
);

逻辑说明 #

1. 内层子查询给所有符合条件的记录打优先级分；
2. 外层查询通过MIN(priority)获取最高优先级，只返回该优先级的记录，实现“pin匹配优先”的效果。

验证示例场景 #

• 当搜索词为Jones时：
  • 方案一的第一个查询返回Kevin（pin匹配），第二个查询因NOT EXISTS不生效，最终仅返回Kevin；
• 当Kevin的pin改为Jones1234时：
  • 第一个查询无结果，第二个查询返回John和Kevin（lastName前缀匹配），符合需求。

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