MySQL两表基于三关联列实现FULL OUTER JOIN异常排查

问题背景 #

需对比两张业务表的汇总数据，现有两个分组查询结果：

第1条查询（返回12行） #

SELECT distinct a.pnr AS PNR, a.station_code as Station,a.date as Date,
sum(a.allamount) as TsrAmount
from daily_posting a 
Where a.date between '2024-06-19' and '2024-06-19' and a.station_code in ('ABVS','BNIA','ABVH') 
group by a.date,a.station_code,a.pnr 

第2条查询（返回17行） #

SELECT distinct b.pnr_NO AS PNR, b.sR_code as Station,b.date_OF_ACTION as Date,
ABS(
sum(b.LOC_TAX_AMOUNT + b.LOC_PAX_FARE + b.LOC_SURCHARGE_AMOUNT + b.LOC_SERVICE_FEE_AMOUNT )) 
as CRAmount 
from ticketsalesdetails b
Where b.date_of_action between '2024-06-19' and '2024-06-19' and b.sr_code in ('ABVS','BNIA','ABVH') 
group by b.date_of_action,b.sr_code,b.pnr_no

需求是通过FULL OUTER JOIN合并两个结果，让金额列并排对比，但自行编写的LEFT JOIN+RIGHT JOIN+UNION查询仅返回12行，且金额数值错误。

错误的FULL OUTER JOIN查询 #

SELECT distinct a.pnr AS PNR, a.station_code as Station,a.date as Date,
sum(a.allamount) as TsrAmount, 
ABS(sum(b.LOC_TAX_AMOUNT + b.LOC_PAX_FARE + b.LOC_SURCHARGE_AMOUNT + b.LOC_SERVICE_FEE_AMOUNT )) as CRAmount 
from daily_posting a LEFT JOIN ticketsalesdetails b 
ON (a.pnr,a.DATE,a.station) = (b.PNR_no,b.date_of_action ,b.sr_code)
Where a.date between '2024-06-19' and '2024-06-19' and a.station_code in ('ABVS','BNIA','ABVH') 
group by a.date,a.station_code,a.pnr 

UNION

SELECT distinct a.pnr AS PNR, a.station_code as Station,a.DATE as Date,
sum(a.allamount) as TsrAmount, -1* sum(b.LOC_TAX_AMOUNT + b.LOC_PAX_FARE + b.LOC_SURCHARGE_AMOUNT + b.LOC_SERVICE_FEE_AMOUNT )  as CRAmount 
from daily_posting a RIGHT JOIN ticketsalesdetails b 
ON (a.pnr,a.DATE,a.station) = (b.PNR_no,b.date_of_action ,b.sr_code)
Where a.date between '2024-06-19' and '2024-06-19' and a.station_code in ('ABVS','BNIA','ABVH') 
group by a.date,a.station_code,a.pnr

问题原因 #

1. RIGHT JOIN过滤了无匹配行：RIGHT JOIN后，仅在ticketsalesdetails存在的记录对应a.date和a.station_code为NULL，WHERE条件会直接过滤掉这些行，导致无法返回额外的5行。
2. 直接关联原表导致重复计算：未预聚合就关联原始表，一对多关联会让SUM计算重复，金额数值失真。
3. 分组字段依赖NULL值：RIGHT JOIN部分的分组字段依赖左表的NULL值，导致分组逻辑错误。

正确解决方案 #

先分别预聚合两张表的汇总数据，再对聚合结果实现FULL OUTER JOIN：

-- 预聚合daily_posting的汇总数据
WITH tsr_data AS (
    SELECT 
        a.pnr AS PNR, 
        a.station_code as Station,
        a.date as Date,
        sum(a.allamount) as TsrAmount
    from daily_posting a 
    Where a.date between '2024-06-19' and '2024-06-19' 
      and a.station_code in ('ABVS','BNIA','ABVH') 
    group by a.date,a.station_code,a.pnr 
),
-- 预聚合ticketsalesdetails的汇总数据
cr_data AS (
    SELECT 
        b.pnr_NO AS PNR, 
        b.sR_code as Station,
        b.date_OF_ACTION as Date,
        ABS(sum(b.LOC_TAX_AMOUNT + b.LOC_PAX_FARE + b.LOC_SURCHARGE_AMOUNT + b.LOC_SERVICE_FEE_AMOUNT )) as CRAmount 
    from ticketsalesdetails b
    Where b.date_of_action between '2024-06-19' and '2024-06-19' 
      and b.sr_code in ('ABVS','BNIA','ABVH') 
    group by b.date_of_action,b.sr_code,b.pnr_no
)
-- 实现FULL OUTER JOIN
SELECT 
    COALESCE(t.PNR, c.PNR) AS PNR,
    COALESCE(t.Station, c.Station) AS Station,
    COALESCE(t.Date, c.Date) AS Date,
    t.TsrAmount,
    c.CRAMount
FROM tsr_data t
LEFT JOIN cr_data c 
    ON t.PNR = c.PNR 
    AND t.Station = c.Station 
    AND t.Date = c.Date

UNION

SELECT 
    COALESCE(t.PNR, c.PNR) AS PNR,
    COALESCE(t.Station, c.Station) AS Station,
    COALESCE(t.Date, c.Date) AS Date,
    t.TsrAmount,
    c.CRAMount
FROM tsr_data t
RIGHT JOIN cr_data c 
    ON t.PNR = c.PNR 
    AND t.Station = c.Station 
    AND t.Date = c.Date;

方案说明 #

1. 预聚合避免重复计算：先计算两张表的分组汇总，确保金额数值准确，避免关联原始表时的重复统计问题。
2. COALESCE处理NULL值：用COALESCE取两个表中不为NULL的标识字段值，保证结果里PNR、Station、Date始终有有效取值。
3. 保留无匹配行：RIGHT JOIN部分不再添加依赖左表的WHERE条件，确保仅在右表存在的记录能被保留。
4. UNION合并完整结果：合并LEFT JOIN和RIGHT JOIN的结果，得到包含两边所有记录的FULL OUTER JOIN效果。

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