左表全保留，两表关联仅取首个匹配结果的实现方案

场景回顾 #

现有两张表：

• pcidTable：包含重复PCID值，需完整保留所有行
• matchTable：已按匹配字符串长度降序排序，需为每个PCID匹配首个（最长）符合条件的Channel

直接关联会因PCID匹配多个子串产生重复行，需确保原表每一行仅关联首个匹配项。

解决方案1：窗口函数法（通用兼容多数SQL方言） #

通过为原表每行生成唯一标识，关联后对匹配项排序筛选首个结果：

WITH numbered_pcid AS (
  -- 为原表每行添加唯一行号，保留所有重复行
  SELECT 
    pcid,
    ROW_NUMBER() OVER () AS row_id
  FROM pcidTable
),
matched_ranked AS (
  -- 关联匹配表，为每个原表行的匹配项排序
  SELECT
    np.row_id,
    np.pcid,
    mt.channel,
    -- 按匹配串长度降序排序，取第一个匹配（RN=1）
    ROW_NUMBER() OVER (PARTITION BY np.row_id ORDER BY LENGTH(mt.match_string) DESC) AS rn
  FROM numbered_pcid np
  LEFT JOIN matchTable mt
    ON REGEXP_CONTAINS(np.pcid, mt.match_string)
)
-- 筛选首个匹配项，保留原表所有行
SELECT pcid, channel
FROM matched_ranked
WHERE rn = 1
ORDER BY pcid;

逻辑说明：

1. numbered_pcid：给pcidTable每行生成唯一row_id，确保重复的PCID（如pcidtest1两行）被独立处理
2. matched_ranked：关联两张表后，用ROW_NUMBER()按row_id分组，对匹配项按串长降序排，rn=1即为首个匹配
3. 最终筛选rn=1的结果，完全符合需求的输出格式

解决方案2：LATERAL/APPLY关联法（简洁高效，支持该语法的数据库） #

利用LATERAL JOIN（PostgreSQL/BigQuery）或CROSS APPLY（SQL Server），对原表每行直接查询首个匹配项：

-- PostgreSQL/BigQuery 写法
SELECT 
  pt.pcid,
  mt.channel
FROM pcidTable pt
LEFT JOIN LATERAL (
  -- 对每个PCID取首个最长匹配的Channel
  SELECT channel
  FROM matchTable mt
  WHERE REGEXP_CONTAINS(pt.pcid, mt.match_string)
  ORDER BY LENGTH(mt.match_string) DESC
  LIMIT 1
) mt ON true;

逻辑说明：

• LATERAL JOIN会为pcidTable的每一行独立执行子查询，直接返回首个匹配的Channel
• 原表的重复行会自动保留，因为每行都单独触发一次子查询
• 性能优于窗口函数法，无需生成所有匹配项再筛选

关键注意点 #

• 因matchTable已按匹配串长度降序，排序时也可直接使用原表的顺序（如ORDER BY mt.original_sort_column），但按长度排序更保险，避免原表排序失效
• 两种方法均未使用自定义函数内的子查询，完全符合限制要求

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