萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 分群彙總:GROUP BY 子句完整用法

GROUP BY 將相同值的資料列分組,搭配彙總函式 (SUM, COUNT, AVG...) 產出摘要報表。

語法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

範例:依客戶統計總訂單金額

SELECT Customer, SUM(OrderPrice) AS TotalAmount
FROM Orders
GROUP BY Customer;

結果

+----------+-------------+
| Customer | TotalAmount |
+----------+-------------+
| Hansen   | 2000        |
| Nilsen   | 1700        |
| Jensen   | 2000        |
+----------+-------------+

多欄位分群

SELECT Customer, YEAR(OrderDate) AS Year, SUM(OrderPrice)
FROM Orders
GROUP BY Customer, YEAR(OrderDate);

關鍵規則

  • SELECT 清單中未使用彙總函式的欄位,必須出現在 GROUP BY 中。
  • WHERE 在分群前過濾,HAVING 在分群後過濾。
  • MySQL 預設 ONLY_FULL_GROUP_BY 模式嚴格執行此規則。

常見錯誤

-- 錯誤:name 不在 GROUP BY 且非彙總
SELECT Customer, OrderDate, SUM(OrderPrice) FROM Orders GROUP BY Customer;

-- 正確:加入彙總或納入 GROUP BY
SELECT Customer, MAX(OrderDate), SUM(OrderPrice) FROM Orders GROUP BY Customer;
copyright © 萬盛學電腦網 all rights reserved