SQL合并含部分公共列的两表时新增指定列避免多余关联记录

现有两张业务表，需要通过关联查询获取目标记录，同时在最终结果中新增uni列，填充uniqueid_5表中对应的留存字段值。原有JOIN逻辑使用or拼接匹配规则，会返回超出需求范围的冗余记录，需要调整SQL写法实现准确查询。

原有问题SQL #

select a.uniqueid,null as uni,b.freetxt,b.free
from (select distinct uniqueid from uniqueid_5) a
inner join freetxt_5 b
on a.uniqueid=b.freetxt or b.freetxt like a.uniqueid+'[ -]%'

原有写法的核心问题：

• 直接将uni列写死为null，没有实际取目标表的对应字段
• 关联条件用or拼接精确匹配和前缀匹配规则时，没有做匹配优先级处理，短uniqueid会误匹配到本应属于更长uniqueid的freetxt记录，导致一条freetxt关联出多条uniqueid，产生冗余结果

修正后SQL #

SELECT 
    uniqueid,
    uni,
    freetxt,
    free
FROM (
    SELECT 
        a.uniqueid,
        a.uni,
        b.freetxt,
        b.free,
        ROW_NUMBER() OVER (
            PARTITION BY b.freetxt, b.free 
            ORDER BY LEN(a.uniqueid) DESC
        ) AS rn
    FROM (SELECT DISTINCT uniqueid, uni FROM uniqueid_5) a
    INNER JOIN freetxt_5 b
        ON b.freetxt = a.uniqueid 
        OR b.freetxt LIKE a.uniqueid + '[ -]%'
) t
WHERE rn = 1;

核心调整逻辑 #

• 子查询中直接从uniqueid_5表取出需要的uni字段，替换原有写死null的写法，直接返回目标列值
• 新增ROW_NUMBER()开窗函数，按freetxt记录分区，以匹配到的uniqueid长度倒序排序，仅保留每个freetxt能匹配到的最长uniqueid关联结果，彻底解决短前缀误匹配导致的冗余记录问题
• 如果freetxt_5表有单独的主键字段，可以把PARTITION BY后的字段替换为主键，分区准确性更高

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