GROUP BY

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

GROUP BY 简单实例

我们先从简单的开始学习，先关注我们的教师表teachers，教师来自不同的国家，我们希望统计不同国家教师的人数，可以使用GROUP BY来统计我们查询的结果，并将结果按照不同国籍教师人数从小到大排列，如果相同教师数量则按照国籍名称排列

SELECT country, COUNT(country) as teacher_count
FROM teachers
GROUP BY country
ORDER BY teacher_count, country;

执行输出结果

mysql> SELECT country, COUNT(country) as teacher_count
    -> FROM teachers
    -> GROUP BY country
    -> ORDER BY teacher_count, country;
+---------+---------------+
| country | teacher_count |
+---------+---------------+
| JP      |             1 |
| UK      |             1 |
| USA     |             1 |
| CN      |             2 |
+---------+---------------+
4 rows in set (0.00 sec)

可以看到我们的结果很好的展示了出来

GROUP BY 多表实例

我们课程表courses每节课程的由一个老师授课，而一个老师对应多门课程，下面我们实现SQL语句，统计每个老师教授课程的学生总数，如果教师没有授课则返回学生上课人数为0

SELECT T.name as teacher_name, IFNULL(SUM(C.student_count),0) as student_count
FROM courses C RIGHT JOIN teachers T
ON C.teacher_id = T.id
GROUP BY T.id;

执行输出结果

mysql> SELECT T.name as teacher_name, IFNULL(SUM(C.student_count),0) as student_count
    -> FROM courses C RIGHT JOIN teachers T
    -> ON C.teacher_id = T.id
    -> GROUP BY T.id;
+------------------+---------------+
| teacher_name     | student_count |
+------------------+---------------+
| Eastern Heretic  |          3200 |
| Northern Beggar  |             0 |
| Western Venom    |          4570 |
| Southern Emperor |          1520 |
| Linghu Chong     |             0 |
+------------------+---------------+
5 rows in set (0.00 sec)

GROUP BY 总结

通过上述案例的学习，我们可以总结出GROUP BY的使用方法

语法

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