MySQL GROUP BY 子句

• 

  GROUP BY子句

  您可以使用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         |
  |   5  | Jill | 2007-04-06 |        220         |
  |   6  | Zara | 2007-06-06 |        300         |
  |   6  | Zara | 2007-02-06 |        350         |
  +------+------+------------+--------------------+
  7 rows in set (0.00 sec)
  

  现在，假设根据上表我们要计算每个员工工作的天数。如果我们将编写如下的SQL查询，那么将得到以下结果：

  mysql> SELECT COUNT(*) FROM employee_tbl;
  +---------------------------+
  |          COUNT(*)         |
  +---------------------------+
  |             7             |
  +---------------------------+

  但这没有达到我们的目的，我们希望显示每个人分别键入的页面总数。这是通过将聚合函数与GROUP BY子句结合使用来完成的，如下所示：

  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)

  或者在用户id上分组

  SELECT id,name, COUNT(*)
     -> FROM   employee_tbl 
     -> GROUP BY id;
  +------+------+----------+
  |  id  | name | COUNT(*) |
  +------+------+----------+
  |   3  | Jack |     2    |
  |   5  | Jill |     1    |
  |   1  | John |     1    |
  |   2  | Ram  |     1    |
  |   6  | Zara |     2    |
  +------+------+----------+
  5 rows in set (0.04 sec)

  我们将在SUM，AVG等其他函数中看到更多与GROUP BY相关的功能。