如何编写MySQL查询关联两张含重复记录的表

我有两张表tbseabuy和tbseasell：

• tbseabuy：存储向供应商采购的费用记录
• tbseasell：存储向客户销售的费用记录

tbseabuy 表结构及数据 #

tbseasell 表结构及数据 #

期望结果 #

需要得到如下格式的查询结果（注：结果中第一行的fhargajual和fhargabeli疑似笔误，按业务逻辑AIR FREIGHT CHARGES为采购记录，应显示fhargabeli=15000、fhargajual=0）：

核心要求 #

• 覆盖所有费用记录，无关联的idcharges仅显示对应存在的fhargajual或fhargabeli
• 关联时以行数较多的表为基准对齐记录

通过为同类型记录添加行号，结合LEFT JOIN和UNION ALL模拟全外连接，实现按多表基准对齐的需求：

WITH buy_with_row AS (
    SELECT 
        fjobkey,
        idcharges,
        fdescription,
        fhargabeli,
        -- 为同一job+费用类型的采购记录生成行号
        ROW_NUMBER() OVER (PARTITION BY fjobkey, idcharges ORDER BY fseq) AS row_num
    FROM tbseabuy
),
sell_with_row AS (
    SELECT 
        fjobkey,
        idcharges,
        fdescription,
        fhargajual,
        -- 为同一job+费用类型的销售记录生成行号
        ROW_NUMBER() OVER (PARTITION BY fjobkey, idcharges ORDER BY fseq) AS row_num
    FROM tbseasell
)
-- 先取所有采购记录及对应销售记录
SELECT 
    COALESCE(b.fjobkey, s.fjobkey) AS fjobkey,
    COALESCE(b.idcharges, s.idcharges) AS idcharges,
    COALESCE(b.fdescription, s.fdescription) AS fdescription,
    COALESCE(s.fhargajual, 0) AS fhargajual,
    COALESCE(b.fhargabeli, 0) AS fhargabeli,
    COALESCE(s.fhargajual, 0) - COALESCE(b.fhargabeli, 0) AS fprofit
FROM buy_with_row b
LEFT JOIN sell_with_row s 
    ON b.fjobkey = s.fjobkey 
    AND b.idcharges = s.idcharges 
    AND b.row_num = s.row_num
UNION ALL
-- 补充采购表中没有的销售记录
SELECT 
    COALESCE(b.fjobkey, s.fjobkey) AS fjobkey,
    COALESCE(b.idcharges, s.idcharges) AS idcharges,
    COALESCE(b.fdescription, s.fdescription) AS fdescription,
    COALESCE(s.fhargajual, 0) AS fhargajual,
    COALESCE(b.fhargabeli, 0) AS fhargabeli,
    COALESCE(s.fhargajual, 0) - COALESCE(b.fhargabeli, 0) AS fprofit
FROM sell_with_row s
LEFT JOIN buy_with_row b 
    ON s.fjobkey = b.fjobkey 
    AND s.idcharges = b.idcharges 
    AND s.row_num = b.row_num
WHERE b.fjobkey IS NULL;

逻辑说明 #

1. 行号标记：通过ROW_NUMBER()为同一fjobkey和idcharges下的记录生成行号，确保多条同类型记录能一一对应，实现以行数多的表为基准对齐
2. 全外连接模拟：MySQL不支持直接的FULL OUTER JOIN，通过两次LEFT JOIN+UNION ALL，先取采购表所有记录，再补充采购表没有的销售记录，覆盖所有数据
3. 空值处理：用COALESCE将不存在的字段值替换为0，保证结果格式统一；fprofit直接通过销售价减采购价计算

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