分组查询_GroupBy

1 作用：按分组的字段将每个分组看作一个整体。
2 语法：select 分组字段，聚合函数 from table group by 分组字段。
注意：查询的字段只能是分组字段或者聚合函数，在分组后查询其他非分组字段时没有一样的。将表格看成是柴火，分组相当于用一条绳子对表格按字段捆绑成一束一束的，你不能查看到某一束中的某根柴火。
3 where 和 having的区别
限定的时机：where在分组前进行限定，不满足where条件的将不参与分组。having在分组后进行限定，不满足条件的将不进行显示。
是否能接聚合函数：where后不能跟聚合函数，having后跟的是聚合函数或者分组的字段。
4 例子：
4.1 查询非分组字段会报错

mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Rose |   22 |   2800 | 114114 | dpt3   |
|  4 | Jim  |   35 |   3000 | 100861 | dpt1   |
|  5 | Mary |   21 |   3000 | 100101 | dpt2   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  9 | Joe  |   31 |   3600 | 110129 | dpt2   |
| 10 | Mike |   23 |   3400 | 110110 | dpt4   |
| 11 | Jobs | NULL |   3600 |  19283 | dpt2   |
| 12 | Tony | NULL |   3400 | 102938 | dpt3   |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)

mysql> select age,in_dpt from employee group by in_dpt;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql_shiyan.employee.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select in_dpt from employee group by in_dpt;
+--------+
| in_dpt |
+--------+
| dpt1   |
| dpt2   |
| dpt3   |
| dpt4   |
+--------+
4 rows in set (0.00 sec)

mysql> select age from employee group by in_dpt;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql_shiyan.employee.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> 

4.2 按部门分组查询各个部门的人数

ysql> select in_dpt,count(id) from employee group by in_dpt;
+--------+-----------+
| in_dpt | count(id) |
+--------+-----------+
| dpt1   |         3 |
| dpt2   |         4 |
| dpt3   |         3 |
| dpt4   |         2 |
+--------+-----------+
4 rows in set (0.00 sec)

4.3 查询各个部门中年龄大于30/小于30的人数

mysql> select in_dpt,count(age)from employee where age > 30 group by in_dpt;
+--------+------------+
| in_dpt | count(age) |
+--------+------------+
| dpt1   |          1 |
| dpt2   |          1 |
+--------+------------+
2 rows in set (0.01 sec)

mysql> select in_dpt,count(age)from employee where age < 30 group by in_dpt;
+--------+------------+
| in_dpt | count(age) |
+--------+------------+
| dpt1   |          2 |
| dpt2   |          2 |
| dpt3   |          2 |
| dpt4   |          2 |
+--------+------------+
4 rows in set (0.00 sec)

4.4 查询各个部门年龄<25,人数大于的情况，having和where的联合使用

mysql> select in_dpt,count(age)from employee where age < 25 group by in_dpt;
+--------+------------+
| in_dpt | count(age) |
+--------+------------+
| dpt2   |          2 |
| dpt3   |          2 |
| dpt4   |          1 |
+--------+------------+
3 rows in set (0.00 sec)

mysql> select in_dpt,count(age) m from employee where age < 25 group by in_dpt having m >1;
+--------+---+
| in_dpt | m |
+--------+---+
| dpt2   | 2 |
| dpt3   | 2 |
+--------+---+
2 rows in set (0.00 sec)