使用Case语句匹配不同长度子串的SQL连接返回重复行问题

要实现每个t1.v匹配t2中最长的前缀子串，同时避免重复行，可以通过窗口函数筛选最长匹配项来解决，无需多连接或coalesce。

解决思路 #

1. 关联两张表，筛选出所有t2.m是t1.v前缀的记录（用LEFT(t1.v, LENGTH(t2.m)) = t2.m判断前缀匹配，比硬写固定长度的substring更通用）；
2. 对每个t1.v，按匹配的t2.m长度倒序排序，用ROW_NUMBER()标记排序后的行号；
3. 只保留行号为1的记录，即为每个t1.v的最长匹配项。

完整SQL代码 #

With t1 as (
  Select 'AAA' as V from dual
  Union all
  Select 'ABA' as V from dual
),
t2 as (
  Select 'A' as m from dual
  Union all 
  Select 'AB' as m from dual
),
matched as (
  Select
    t1.v,
    t2.m,
    ROW_NUMBER() OVER (PARTITION BY t1.v ORDER BY LENGTH(t2.m) DESC) as rn
  From t1
  Left join t2 on LEFT(t1.v, LENGTH(t2.m)) = t2.m
)
Select v, m from matched where rn = 1;

执行结果 #

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