MySQL中如何按指定列优先级匹配关联表并筛选数据

要实现优先保留store_code匹配的记录，仅当无匹配时保留sub_channel匹配记录的需求，你可以通过以下两种方式修改查询语句：

方法一：使用窗口函数（推荐，简洁高效） #

利用ROW_NUMBER()窗口函数为每条匹配记录标记优先级，仅保留同一店铺同一商品的最高优先级记录：

SELECT store_name, t2.*
FROM (
    SELECT 
        t1.store_name, 
        t2.*,
        -- 标记优先级：store_code匹配为1，sub_channel匹配为2
        ROW_NUMBER() OVER (
            PARTITION BY t1.store_name, t2.product_code 
            ORDER BY CASE WHEN t2.store_code = t1.store_code THEN 1 ELSE 2 END
        ) AS rn
    FROM table1 AS t1
    LEFT JOIN table2 AS t2 
        ON t2.store_code = t1.store_code OR t2.sub_channel = t1.sub_channel
) AS temp
WHERE rn = 1 -- 仅保留优先级最高的记录
ORDER BY store_name, product_code;

逻辑说明 #

• PARTITION BY t1.store_name, t2.product_code：按店铺名称和商品编码分组，确保同一店铺下的同一商品只保留一条记录
• ORDER BY CASE ...：给store_code匹配的记录赋予更高优先级（排名1），sub_channel匹配的记录排名2
• 外层过滤rn = 1，只保留每组中优先级最高的记录

方法二：使用UNION ALL兼容低版本数据库 #

如果你的数据库不支持窗口函数（如MySQL 5.x），可以通过先获取高优先级记录，再补充无高优先级匹配的低优先级记录的方式实现：

-- 第一步：获取store_code匹配的所有记录
SELECT t1.store_name, t2.*
FROM table1 AS t1
JOIN table2 AS t2 ON t2.store_code = t1.store_code
UNION ALL
-- 第二步：获取sub_channel匹配且该商品无store_code匹配的记录
SELECT t1.store_name, t2.*
FROM table1 AS t1
JOIN table2 AS t2 ON t2.sub_channel = t1.sub_channel
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 AS t2_exists
    WHERE t2_exists.product_code = t2.product_code 
      AND t2_exists.store_code = t1.store_code
)
ORDER BY store_name, product_code;

逻辑说明 #

• 先通过JOIN获取所有store_code匹配的高优先级记录
• 再通过NOT EXISTS筛选出当前店铺下该商品无store_code匹配的sub_channel记录，补充到结果集中
• UNION ALL确保不会去重，同时避免重复记录

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