SQL调整需求：对比mq_billing与mq_paymentlist取最大客户总数

你需要对原有SQL做两处核心修改：

• 给mq_paymentlist的聚合子查询新增客户数统计逻辑，得到该表对应的各主体客户数
• 外层查询调用GREATEST()函数，取两个表统计的客户数的最大值作为最终结果

调整后的完整SQL如下：

SELECT b.Entity_Name, 
       GREATEST(b.`Billing Total Customer`, p.`Payment Total Customer`) AS `Total Customer`,
       b.`Invoice Amount`,
       p.`Online Payment`,
       p.`Cash Payment`,
       p.`Total Payment`
FROM (
  SELECT Entity_Name, 
         COUNT(Customer_Nbr) AS `Billing Total Customer`,
         SUM(Invoice_Amount) AS `Invoice Amount`
  FROM mq_billing
  GROUP BY Entity_Name
) b INNER JOIN (
  SELECT Entity_Name, 
         COUNT(Customer_Nbr) AS `Payment Total Customer`,
         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
ORDER BY b.Entity_Name;

注意：如果你需要覆盖仅出现在单个表中的主体数据，可以将INNER JOIN替换为FULL OUTER JOIN，同时配合COALESCE函数处理空值，避免GREATEST计算出现空结果。

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