MySQL GROUP_CONCAT在子查询中具有SUM（）和多个JOIN

debugcn 发表于 Dev

毒蛇

我对MySQL非常平均，但是通常我可以在阅读文档并搜索示例之后编写所有需要的查询。现在，我处于花了三天的时间进行重新搜索和重新编写查询的情况，但是我无法使其完全按照我需要的方式工作。这是交易：

第一个表（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                 |

第三张表（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一对多关系：GROUP_CONCAT或JOIN或两者兼有？但是花了2个小时重新编写了提供的查询以处理我的数据后，我无法做到。

有人可以给我一个建议吗？

粘性位

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

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 <>小提琴

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。