mysql - MySQL 中的复杂 SUM

Question

嘿，

我有 2 个表，客户和供应商，列数相同

Client | Amount | Currency
Supplier | Amount | Currency

某一时刻的所有客户都可能是供应商和逆转。我想要一个查询，该查询应按照以下规则计算两个表中不同合作伙伴的值:

Client Amount - Supplier Amount = Total Amount

最终的表格应如下所示:

Partner | Currency | Amount Client | Amount Supplier | Total Amount

客户端表:

Client | Amount | Currency
P1     | 100    | EUR
P2     | 50     | USD
P1     | 125    | EUR
P2     | 25     | EUR

供应商表

Supplier | Amount | Currency
P1       | 75     | EUR
P3       | 125    | USD
P2       | 50     | EUR
P1       | 75     | USD

预期输出应该是:

Partner | Currency | Amount to receive | Amount to pay | Total amount
P1      | EUR      | 225               | 75            | 150
P1      | USD      | 0                 | 75            | -75
P2      | EUR      | 25                | 50            | -25
P2      | USD      | 50                | 0             | 50
P3      | USD      | 0                 | 125           | -125 

Answer 1

通过在两个表之间执行UNION ALL，您可以获得所有可能的合作伙伴代码的完整列表，以及它们各自的金额和货币。

但是，这还不足以区分他们的角色。在 UNION 查询中添加一列，该列提供一个简单的静态字符串，用于标识角色是作为 Client 还是 Supplier。然后，外部查询可以在 SUM() 聚合内部使用 CASE 汇总这些内容，以确定要添加哪些角色。

<强> Here is a demonstration of this in action...

SELECT 
  Partner,
  Currency,
  -- Based on the static string value added in the UNION, determine
  -- whether Amount should be summarized, or just add 0
  -- This results in zeros instead of NULL for empty values, so it 
  -- has the added benefit of not requiring COALESCE()
  SUM(CASE WHEN type = 'Client' THEN Amount ELSE 0 END) AS `Amount to receive`,
  SUM(CASE WHEN type = 'Supplier' THEN Amount ELSE 0 END) AS `Amount to pay`,
  (SUM(CASE WHEN type = 'Client' THEN Amount ELSE 0 END)
  - SUM(CASE WHEN type = 'Supplier' THEN Amount ELSE 0 END)) AS `Total amount`
FROM (
  -- A UNION ALL combines all the Client/Supplier rows
  SELECT
    -- With a static string column added to identify the role
    -- (the source table)
    'Client' AS type,
    Client AS Partner,
    Amount,
    Currency
  FROM Client
  UNION ALL
  SELECT
    'Supplier' AS type,
    Supplier AS Partner,
    Amount,
    Currency
  FROM Supplier
) all_cs
GROUP BY 
  Partner,
  Currency