MySQL实现两表匹配后移除匹配项的单次关联查询需求

可以实现这种“匹配后排除”的关联逻辑，核心思路是通过窗口函数为每组匹配记录添加唯一序号，然后基于序号进行一对一关联，避免重复匹配。

具体实现步骤 #

针对materials（生产物料表）和components（生产组件表）的结构，我们给两个表中属于同一匹配组（按refcode和bomlevel分组）的记录分别分配递增序号，再通过序号相等的条件完成关联，确保每条记录仅匹配一次。

完整查询语句 #

SELECT 
    m.refcode,
    m.bomlevel,
    m.nextbomlevel,
    c.item
FROM (
    -- 给materials表的每组记录添加序号
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY refcode, bomlevel 
            ORDER BY nextbomlevel -- 可根据实际需求调整排序字段
        ) AS rn
    FROM materials
) m
LEFT JOIN (
    -- 给components表的每组记录添加序号
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY refcode, bomlevel 
            ORDER BY item -- 可根据实际需求调整排序字段
        ) AS rn
    FROM components
) c 
    ON m.refcode = c.refcode 
    AND m.bomlevel = c.bomlevel 
    AND m.rn = c.rn;

逻辑说明 #

1. 窗口函数分组排序：PARTITION BY refcode, bomlevel确保只对同一物料、同一层级的记录分组；ORDER BY子句用于确定组内记录的匹配顺序，可根据业务需求调整（比如按主键、字段值排序等）。
2. 序号关联：通过m.rn = c.rn的条件，让两个表中每组内的第1条记录匹配、第2条记录匹配，以此类推，彻底避免了一对多关联产生的重复匹配结果。

版本兼容 #

该方案依赖MySQL 8.0及以上版本的窗口函数特性，如果使用更低版本的MySQL，可以通过用户自定义变量模拟序号生成逻辑，核心思路一致。

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