对行分组
GROUP BY子句与SELECT语句一起使用，使用聚合函数按某个字段对结果集中的行分组。
让我们看下面的employees和departments表。
employees
+--------+--------------+------------+---------+
| emp_id | emp_name | hire_date | dept_id |
+--------+--------------+------------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
+--------+--------------+------------+---------+
departments
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
现在，我们要查询每个部门的员工总数。
可以使用GROUP BY子句和SELECT语句查询，如下所示:
SELECT t1.dept_name, count(t2.emp_id) AS total_employees
FROM departments AS t1 LEFT JOIN employees AS t2
ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_name;
如果你执行上面的语句，你会得到这样的输出:
+-------------------+-----------------+
| dept_name | total_employees |
+-------------------+-----------------+
| Administration | 1 |
| Customer Service | 0 |
| Finance | 1 |
| Human Resources | 1 |
| Sales | 1 |
+-------------------+-----------------+
注意:
注意: 在SELECT语句中，GROUP BY子句必须出现在FROM和WHERE子句之后，ORDER BY子句之前。