三表关联计算百分比金额及替换INNER JOIN获取全量记录求助

问题排查&错误点说明 #

• 第一个错误：第三个计算支付占比的子查询内部，直接引用了未定义的别名e.Percentage，该别名是你准备给这个子查询最终设置的名称，子查询运行时该别名还未生效，属于语法错误。
• 第二个错误：mq_entity表本身没有Payment_Mode、Amount字段，这两个字段存储在mq_paymentlist表中，在mq_entity的查询逻辑里对这两个字段做SUM计算，必然执行失败。
• 第三个错误：占比计算逻辑不需要单独嵌套子查询，参考你提供的可正常运行的SQL，直接在外层SELECT里新增计算字段即可，冗余嵌套反而会增加出错概率。

修正后可正常运行的SQL #

SELECT 
    COALESCE(b.Entity_Name, p.Entity_Name, e.Entity_Name) AS Entity_Name,
    e.Percentage AS 'Entity (%)',
    (100 - e.Percentage) AS 'Own (%)',
    IFNULL(b.`No. of Invoice`, 0) AS `No. of Invoice`,
    IFNULL(b.`Total Invoice`, 0) AS `Total Invoice`,
    IFNULL(p.`No. of Payment`, 0) AS `No. of Payment`,
    IFNULL(p.`Online Payment`, 0) AS `Online Payment`,
    IFNULL(p.`Cash Payment`, 0) AS `Cash Payment`,
    -- 直接在外层计算占比，可自行调整ROUND的保留精度或者删除ROUND函数
    ROUND((p.`Online Payment` * e.Percentage) / 100, 2) AS `Reseller (% Online)`,
    ROUND((p.`Online Payment` * (100 - e.Percentage)) / 100, 2) AS `Own (% Online)`,
    ROUND((p.`Cash Payment` * e.Percentage) / 100, 2) AS `Reseller (% Cash)`,
    ROUND((p.`Cash Payment` * (100 - e.Percentage)) / 100, 2) AS `Own (% Cash)`,
    IFNULL(p.`Total Payment`, 0) AS `Total Payment`
FROM (
    SELECT Entity_Name, 
           COUNT(Customer_Nbr) AS `No. of Invoice`,
           SUM(Invoice_Amount) AS `Total Invoice`
    FROM mq_billing
    GROUP BY Entity_Name
) b 
-- 替换INNER JOIN为LEFT JOIN，保留所有账单主体，关联不到的支付数据默认显示为0
LEFT JOIN (
    SELECT COUNT(Customer_Nbr) AS 'No. of Payment',
         Entity_Name,
         SUM(CASE WHEN Payment_Mode = 'Online Payment' THEN Amount ELSE 0 END) AS `Online Payment`,
         SUM(CASE WHEN Payment_Mode = 'Cash' THEN Amount ELSE 0 END) AS `Cash Payment`,
         SUM(Amount) AS `Total Payment`
    FROM mq_paymentlist
    GROUP BY Entity_Name
) p ON b.Entity_Name = p.Entity_Name
-- 同理替换为LEFT JOIN，保留前面的所有主体，关联不到的主体占比可自行调整默认值
LEFT JOIN mq_entity e on COALESCE(b.Entity_Name, p.Entity_Name) = e.Entity_Name

-- 如果需要同时保留只有支付数据、没有账单数据的主体，保留下面UNION部分即可，不需要可以删除
UNION

SELECT 
    COALESCE(b.Entity_Name, p.Entity_Name, e.Entity_Name) AS Entity_Name,
    e.Percentage AS 'Entity (%)',
    (100 - e.Percentage) AS 'Own (%)',
    IFNULL(b.`No. of Invoice`, 0) AS `No. of Invoice`,
    IFNULL(b.`Total Invoice`, 0) AS `Total Invoice`,
    IFNULL(p.`No. of Payment`, 0) AS `No. of Payment`,
    IFNULL(p.`Online Payment`, 0) AS `Online Payment`,
    IFNULL(p.`Cash Payment`, 0) AS `Cash Payment`,
    ROUND((p.`Online Payment` * e.Percentage) / 100, 2) AS `Reseller (% Online)`,
    ROUND((p.`Online Payment` * (100 - e.Percentage)) / 100, 2) AS `Own (% Online)`,
    ROUND((p.`Cash Payment` * e.Percentage) / 100, 2) AS `Reseller (% Cash)`,
    ROUND((p.`Cash Payment` * (100 - e.Percentage)) / 100, 2) AS `Own (% Cash)`,
    IFNULL(p.`Total Payment`, 0) AS `Total Payment`
FROM (
    SELECT Entity_Name, 
           COUNT(Customer_Nbr) AS `No. of Invoice`,
           SUM(Invoice_Amount) AS `Total Invoice`
    FROM mq_billing
    GROUP BY Entity_Name
) b 
RIGHT JOIN (
    SELECT COUNT(Customer_Nbr) AS 'No. of Payment',
         Entity_Name,
         SUM(CASE WHEN Payment_Mode = 'Online Payment' THEN Amount ELSE 0 END) AS `Online Payment`,
         SUM(CASE WHEN Payment_Mode = 'Cash' THEN Amount ELSE 0 END) AS `Cash Payment`,
         SUM(Amount) AS `Total Payment`
    FROM mq_paymentlist
    GROUP BY Entity_Name
) p ON b.Entity_Name = p.Entity_Name
LEFT JOIN mq_entity e on COALESCE(b.Entity_Name, p.Entity_Name) = e.Entity_Name
WHERE b.Entity_Name IS NULL

ORDER BY Entity_Name;

调整说明 #

• 占比计算逻辑移到最外层SELECT，直接调用已经聚合完成的支付金额和mq_entity的Percentage字段计算，解决了原SQL的字段不存在报错问题。
• 原INNER JOIN替换为LEFT JOIN+UNION+RIGHT JOIN的形式，实现了全外连接效果，所有主体无论有没有账单/支付记录都会在结果中展示，没有对应数据的字段默认用0填充，可根据业务需求自行调整默认值。
• 用COALESCE函数统一获取主体名称，避免关联失败场景下主体名称显示为空的问题。

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