MySQL分組求和后再求和

如果您需要對已經分組的數據進行匯總，MySQL分組求和后再求和就是一個不錯的選擇。以下是一些例子。

基本語法

將分組結果作為子查詢，并將結果用SUM函數匯總：

SELECT SUM(subtotal) as grand_total FROM (SELECT SUM(total) as subtotal FROM orders GROUP BY customer_id) as sub_query;

其中，INNER JOIN可用于以不同維度聚合不同表的數據。

多列聚合

您可以使用GROUP BY子句指定要聚合的多個列，并在子查詢和主查詢中使用相應的列別名：

SELECT SUM(sub_customer_purchase) as customer_purchase_total, SUM(sub_item_sold) as item_sold_total FROM (SELECT customer_id, COUNT(*) as sub_customer_purchase, SUM(item_quantity) as sub_item_sold FROM orders GROUP BY customer_id, item_id) as sub_query;

篩選聚合結果

您可以使用WHERE子句篩選聚合結果：

SELECT SUM(subtotal) as grand_total FROM (SELECT SUM(total) as subtotal FROM orders GROUP BY customer_id) as sub_query WHERE grand_total >500;

計算百分比

您可以使用CONCAT和ROUND函數將百分比格式化為整數：

SELECT CONCAT(ROUND(SUM(subtotal) / grand_total * 100), '%') as percentage FROM (SELECT SUM(total) as subtotal FROM orders WHERE YEAR(order_date) = 2021 GROUP BY MONTH(order_date)) as sub_query, (SELECT SUM(total) as grand_total FROM orders WHERE YEAR(order_date) = 2021) as grand_query;

在這個例子中，我們計算了2021年訂單的每個月份的總收入，并使用連接（joining）將其除以年度總收入（由另一個子查詢給出）。

結論

無論您想要對您的數據進行何種聚合計算，MySQL分組求和后再求和都是一個靈活有效的解決方案。使用這個技巧，您可以輕松地將數據歸納為更高維度的摘要信息。