PostgreSQL LIKE查询重复结果问题：如何精准匹配子编码？

问题分析 #

当前关联逻辑中，TB01Q07a这类子编码会同时匹配question表中的TB01Q07（前缀匹配）和TB01Q07a（完全匹配），导致同一question_detail记录被重复返回。核心需求是为每条question_detail记录找到最长匹配的question编码，避免重复。

解决方案 #

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

通过ROW_NUMBER()窗口函数，对每个question_detail记录的所有匹配结果按question.code长度倒序排序，只保留排序后的第一条记录（即最长匹配的编码）：

SELECT code, label, type
FROM (
    SELECT qd.code, qd.label, q.type,
           -- 按question.code长度倒序，确保最长匹配排第一
           ROW_NUMBER() OVER (PARTITION BY qd.code ORDER BY LENGTH(q.code) DESC) AS rn
    FROM public.question q
    JOIN public.question_detail qd 
      ON qd.code LIKE CONCAT(q.code, '%')
) sub_query
WHERE rn = 1;

方法2：使用NOT EXISTS排除更长匹配 #

通过NOT EXISTS子查询，确保当前匹配的question.code是该question_detail记录能匹配到的最长编码：

SELECT qd.code, qd.label, q.type
FROM public.question q
JOIN public.question_detail qd 
  ON qd.code LIKE CONCAT(q.code, '%')
WHERE NOT EXISTS (
    SELECT 1
    FROM public.question q2
    -- q2.code是q.code的扩展，同时也是qd.code的前缀
    WHERE q2.code LIKE CONCAT(q.code, '%')
      AND q2.code != q.code
      AND qd.code LIKE CONCAT(q2.code, '%')
);

结果验证 #

执行以上任一SQL，都会得到预期结果：

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