mysql - 通过加入 3 个表格计算百分比明智金额。还需要更改 INNER JOIN 以获取 COMMON 和 UNCOMMON 记录

Question

我知道这很长，但请多多包涵。我将加入 3 个表格来计算每个实体的在线百分比和现金金额。我尝试关注但不工作。任何人都可以帮助我吗？

我还需要替换 INNER JOIN 以便我可以从两个表中获取所有 COMMON 或 UNCOMMON 结果。

SELECT b.Entity_Name, 
       e.Percentage AS 'Entity (%)',
       (100 - e.Percentage) AS 'Own (%)',
       b.`No. of Invoice`,
       b.`Total Invoice`,
       p.`No. of Payment`,
       p.`Online Payment`,
       p.`Cash Payment`,
       e.`Reseller (% Online)`,
       e.`Own (% Online)`,
       e.`Reseller (% Cash)`,
       e.`Own (% Cash)`,
       p.`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 (
  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
  INNER JOIN (
  SELECT Entity_Name,
  ((SUM(CASE WHEN Payment_Mode = 'Online Payment' THEN Amount ELSE 0 END) * e.Percentage) / 100) AS `Reseller (% Online)`,
  ((SUM(CASE WHEN Payment_Mode = 'Online Payment' THEN Amount ELSE 0 END) * (100 - e.Percentage)) / 100) AS `Own (% Online)`,
  ((SUM(CASE WHEN Payment_Mode = 'Cash' THEN Amount ELSE 0 END) * e.Percentage) / 100) AS `Reseller (% Cash)`,
  ((SUM(CASE WHEN Payment_Mode = 'Cash' THEN Amount ELSE 0 END) * (100 - e.Percentage)) / 100) AS `Own (% Cash)`
  FROM mq_entity
  GROUP BY Entity_Name
  ) e on p.Entity_Name = e.Entity_Name
ORDER BY b.Entity_Name;

以下代码仅在尝试显示百分比时有效。

SELECT b.Entity_Name, 
       e.Percentage AS 'Entity (%)',
       (100 - e.Percentage) AS 'Own (%)',
       b.`No. of Invoice`,
       b.`Total Invoice`,
       p.`No. of Payment`,
       p.`Online Payment`,
       p.`Cash Payment`,
       p.`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 (
  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
INNER JOIN mq_entity e on p.Entity_Name = e.Entity_Name
ORDER BY b.Entity_Name;

标签： mysqlsql