可以使用 GROUP BY 子句對列中的值進(jìn)行分組。如果你愿意,還可以對列實施某種計算??梢詫Ψ纸M的列使用COUNT、SUM以及AVG等函數(shù)。
為了理解 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)
現(xiàn)在,根據(jù)上面這張表,我們來計算一下每位員工的工作天數(shù)。
如果像下面這樣寫 SQL 查詢,那么將得到下列結(jié)果:
mysql> SELECT COUNT(*) FROM employee_tbl;
+---------------------------+
| COUNT(*) |
+---------------------------+
| 7 |
+---------------------------+
但這并不符合預(yù)期,我們希望的是輸出每位員工輸入的頁數(shù)??梢越Y(jié)合使用聚合函數(shù)與 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)
以后,我們還將介紹更多 GROUP BY 與 SUM、AVG 等函數(shù)相結(jié)合的用法。