基于COL2分组的MYSQL Col1 Sum

Question

我正在查询 Wordpress postmeta 表以尝试按客户提取一些销售信息。我怎样才能让客户组合在一起。这里的挑战是所有数据都在 postmeta 表中。

这是我迄今为止不成功的尝试:

SELECT CONCAT_WS(" ", metaA.meta_value, metaB.meta_value) AS customer_name, metaC.meta_value AS 'Total'
FROM wp_postmeta AS metaA
JOIN wp_postmeta AS metaB USING (post_id)
JOIN wp_postmeta AS metaC USING (post_id)
WHERE (metaA.meta_key = '_billing_first_name')
AND (metaB.meta_key = '_billing_last_name')
AND (metaC.meta_key = '_order_total')
GROUP BY metaA.meta_value, metaB.meta_value, metaC.meta_value

结果:

+---------------+---------+
| customer_name |  Total  |
+---------------+---------+
|     customer1 |      10 |
|     customer1 |      50 |
|     customer1 |      90 |
|     customer2 |      40 |
|     customer3 |      10 |
|     customer3 |      10 |
|     customer4 |      20 |
+---------------+---------+

我想得到:

+---------------+---------+
| customer_name |  Total  |
+---------------+---------+
|     customer1 |     150 |
|     customer2 |      40 |
|     customer3 |      20 |
|     customer4 |      20 |
+---------------+---------+

Answer 1

您需要按串联的客户名称进行分组并对 metaC.meta_value 求和:

SELECT CONCAT_WS(" ", metaA.meta_value, metaB.meta_value) AS customer_name, sum(metaC.meta_value) AS 'Total'
FROM wp_postmeta AS metaA
JOIN wp_postmeta AS metaB USING (post_id)
JOIN wp_postmeta AS metaC USING (post_id)
WHERE (metaA.meta_key = '_billing_first_name')
    AND (metaB.meta_key = '_billing_last_name')
    AND (metaC.meta_key = '_order_total')
GROUP BY CONCAT_WS(" ", metaA.meta_value, metaB.meta_value)