编写SQL查询实现匹配列值优先排序并保留全部数据行

原始表结构与数据 #

COLA COLB COLC COLD
100  100  AA   null
100  100  CC   BB
100  100  DD   CC
100  100  EE   DD
100  100  null FF
100  100  FF   GG
100  100  HH   null

期望输出 #

COLA COLB COLC COLD
100  100  AA   null
100  100  CC   CC
100  100  DD   DD
100  100  FF   FF
100  100  EE   BB
100  100  null GG
100  100  HH   null

问题分析 #

你之前用INNER JOIN只会返回匹配的行，无法保留未匹配的记录。要实现需求，需要用LEFT JOIN或EXISTS子查询来关联数据，同时保留所有行，再通过排序规则将匹配行前置。

解决方案1：使用EXISTS子查询 #

SELECT 
    t.COLA,
    t.COLB,
    t.COLC,
    -- 若当前COLC存在于其他行的COLD中，替换COLD为COLC，否则保留原值
    CASE 
        WHEN EXISTS (SELECT 1 FROM temp t2 WHERE t2.COLD = t.COLC) 
        THEN t.COLC 
        ELSE t.COLD 
    END AS COLD
FROM temp t
ORDER BY 
    -- 匹配行优先排序（0在前，1在后）
    CASE 
        WHEN EXISTS (SELECT 1 FROM temp t2 WHERE t2.COLD = t.COLC) 
        THEN 0 
        ELSE 1 
    END,
    -- 按COLC排序保证输出顺序一致
    COLC;

解决方案2：使用LEFT JOIN关联 #

SELECT 
    t.COLA,
    t.COLB,
    t.COLC,
    -- 匹配到则用COLC替换COLD，否则保留原COLD
    COALESCE(t_match.COLC, t.COLD) AS COLD
FROM temp t
LEFT JOIN temp t_match ON t_match.COLD = t.COLC
ORDER BY 
    -- 匹配行前置
    CASE WHEN t_match.COLC IS NOT NULL THEN 0 ELSE 1 END,
    t.COLC;

逻辑说明 #

1. 值替换：两种方式都会判断当前行的COLC是否存在于其他行的COLD中，若存在则将COLD替换为COLC的值，否则保留原始COLD。
2. 排序规则：通过ORDER BY中的条件，让匹配成功的行（标记为0）排在未匹配行（标记为1）前面，再按COLC排序保证输出顺序与期望一致。

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