带有 SUM() 的 MySQL GROUP_CONCAT 和子查询内的多个 JOIN

Question

我对 MySQL 的使用非常一般，但通常我可以在阅读文档和搜索示例后编写所有需要的查询。现在，我处于我花了 3 天时间重新搜索和重新编写查询的情况，但我无法让它按照我需要的方式工作。这是交易:

第一个表 (mpt_companies) 包含公司:

| company_id | company_title |
------------------------------
| 1          | Company A     |
| 2          | Company B     |

第二个表 (mpt_payment_methods) 包含付款方式:

| payment_method_id | payment_method_title |
--------------------------------------------
| 1                 | Cash                 |
| 2                 | PayPal               |
| 3                 | Wire                 |

第 3 个表 (mpt_payments) 包含每个公司的付款:

| payment_id | company_id | payment_method_id | payment_amount |
----------------------------------------------------------------
| 1          | 1          | 1                 | 10.00          |
| 2          | 2          | 3                 | 15.00          |
| 3          | 1          | 1                 | 20.00          |
| 4          | 1          | 2                 | 10.00          |

我需要列出每家公司以及许多统计数据。统计数据之一是每种付款方式的付款总和。换句话说，结果应该是:

| company_id | company_title | payment_data            |
--------------------------------------------------------
| 1          | Company A     | Cash:30.00,PayPal:10.00 |
| 2          | Company B     | Wire:15.00              |

显然，我需要:

• 选择所有公司；
• 为每家公司联合付款；
• 加入每笔付款的付款方式；
• 计算每种方法的付款总和；
• GROUP_CONCAT 付款方式和金额；

不幸的是，SUM() 不适用于 GROUP_CONCAT。我在此站点上找到的一些解决方案建议使用 CONCAT，但这不会生成我需要的列表。其他解决方案建议使用 CAST()，但也许我做错了什么，因为它也不起作用。这是我写的最接近的查询，它返回每家公司，以及每家公司使用的唯一付款方式列表，但不返回付款总和:

SELECT *,
(some other sub-queries I need...),
(SELECT GROUP_CONCAT(DISTINCT(mpt_payment_methods.payment_method_title))
FROM mpt_payments
JOIN mpt_payment_methods
ON mpt_payments.payment_method_id=mpt_payment_methods.payment_method_id
WHERE mpt_payments.company_id=mpt_companies.company_id
ORDER BY mpt_payment_methods.payment_method_title) AS payment_data
FROM mpt_companies

然后我尝试了:

SELECT *,
(some other sub-queries I need...),
(SELECT GROUP_CONCAT(DISTINCT(mpt_payment_methods.payment_method_title), ':',  CAST(SUM(mpt_payments.payment_amount) AS CHAR))
FROM mpt_payments
JOIN mpt_payment_methods
ON mpt_payments.payment_method_id=mpt_payment_methods.payment_method_id
WHERE mpt_payments.company_id=mpt_companies.company_id
ORDER BY mpt_payment_methods.payment_method_title) AS payment_data
FROM mpt_companies

...以及许多其他变体，但它们要么返回查询错误，要么没有返回/格式化我需要的数据。

我能找到的最接近的答案是 MySQL one to many relationship: GROUP_CONCAT or JOIN or both?但在花了 2 小时重写提供的查询以处理我的数据后，我无法做到。

谁能给我一个建议，好吗？

Answer 1

您可以通过聚合两次来做到这一点。首先是每个方法和公司的付款总和，然后连接每个公司的总和。

SELECT x.company_id,
       x.company_title,
       group_concat(payment_amount_and_method) payment_data
       FROM (SELECT c.company_id,
                    c.company_title,
                    concat(pm.payment_method_title, ':', sum(p.payment_amount)) payment_amount_and_method
                    FROM mpt_companies c
                         INNER JOIN mpt_payments p
                                    ON p.company_id = c.company_id
                         INNER JOIN mpt_payment_methods pm
                                    ON pm.payment_method_id = p.payment_method_id
                    GROUP BY c.company_id,
                             c.company_title,
                             pm.payment_method_id,
                             pm.payment_method_title) x
      GROUP BY x.company_id,
               x.company_title;

db<>fiddle