可以使用GROUP BY組值一列,並且如果願意的話,可以將該列進行計算。使用COUNT,SUM,AVG等功能的分組列。
要了解GROUP BY子句考慮的EMPLOYEE_TBL的的表具有以下記錄:
mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram | 2007-05-27 | 220 | | 3 | Jack | 2007-05-06 | 170 | | 3 | Jack | 2007-04-06 | 100 | | 4 | Jill | 2007-04-06 | 220 | | 5 | Zara | 2007-06-06 | 300 | | 5 | Zara | 2007-02-06 | 350 | +------+------+------------+--------------------+ 7 rows in set (0.00 sec)
現在,假設根據上表,我們希望計算每一位員工工作的天數。
如果我們將編寫一個SQL查詢,如下所示,那麼我們將得到下面的結果:
mysql> SELECT COUNT(*) FROM employee_tbl; +---------------------------+ | COUNT(*) | +---------------------------+ | 7 | +---------------------------+
但是,這不是我們的目的服務,我們要顯示輸入的每個人單獨的頁面總數。這是通過使用聚合函數一起用GROUP BY子句如下:
mysql> SELECT name, COUNT(*) -> FROM employee_tbl -> GROUP BY name; +------+----------+ | name | COUNT(*) | +------+----------+ | Jack | 2 | | Jill | 1 | | John | 1 | | Ram | 1 | | Zara | 2 | +------+----------+ 5 rows in set (0.04 sec)