mysql - 如何根据 MySQL 中另一列的值输出包含组合总数的行？

Question

我有这个查询，它导致包含订单总额和客户 ID 的行。

SELECT postMetaOrderTotal.meta_value as 'order_total', postMetaCustomerUser.meta_value as 'customer_user' FROM wp_posts as posts
LEFT JOIN wp_postmeta as postMetaOrderTotal ON postMetaOrderTotal.post_id=posts.ID
LEFT JOIN wp_postmeta as postMetaCustomerUser ON postMetaCustomerUser.post_id=posts.ID
LEFT JOIN wp_users as users ON users.ID = postMetaCustomerUser.meta_value
WHERE posts.post_type = 'shop_order'  
AND postMetaOrderTotal.meta_key = '_order_total' 
AND postMetaCustomerUser.meta_key = '_customer_user'

结果是：

我怎样才能让结果成为每个客户用户的总数？（例如，在屏幕截图中，客户 74 的 2 行将是 1 与合并订单总数）？

我查看了 GROUP BY 语句（https://www.guru99.com/group-by.html）并分组，customer_user但这些结果为客户 0 的 1 行

标签： mysqldatabase

Answer 1

如果我理解正确，您可以使用SUMand GROUP BY：

SELECT customer_user, SUM(order_total) GROUP BY custom_user;

因此，对于您的原始查询，这样的事情可能会起作用：

SELECT SUM(postMetaOrderTotal.meta_value) as 'order_total', postMetaCustomerUser.meta_value as 'customer_user' 
FROM wp_posts as posts
LEFT JOIN wp_postmeta as postMetaOrderTotal ON postMetaOrderTotal.post_id=posts.ID
LEFT JOIN wp_postmeta as postMetaCustomerUser ON postMetaCustomerUser.post_id=posts.ID
LEFT JOIN wp_users as users ON users.ID = postMetaCustomerUser.meta_value
WHERE posts.post_type = 'shop_order'  
AND postMetaOrderTotal.meta_key = '_order_total' 
AND postMetaCustomerUser.meta_key = '_customer_user'
GROUP BY custom_user;

如果您想完全排除客户 0，那么只需使用WHERE. 如果您不想使用客户 0 的数据，则必须使用UNION.