【MySQL】select,group by聚合查询用法-进阶（详解）

group by其实是以行为运算的，就是联合的是行，而不是列，下面我们来举个例子：

mysql> create table salary (id int primary key auto_increment,name varchar(20),pay_for_per_month int,position varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc salary
    -> ;
+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | int         | NO   | PRI | NULL    | auto_increment |
| name              | varchar(20) | YES  |     | NULL    |                |
| pay_for_per_month | int         | YES  |     | NULL    |                |
| position          | varchar(20) | YES  |     | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into salary values(null,'z1','10000','JAVA研发');
Query OK, 1 row affected (0.01 sec)

mysql> insert into salary values(null,'z2','11000','JAVA研发');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z3','17000','JAVA研发');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z4','18000','c++研发');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z5','8000','c++研发');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z6','6000','嵌入式开发');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z7','8000','嵌入式开发');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z8','9000','嵌入式开发');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z9','12000','算法工程师');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z10','15000','算法工程师');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z11','23000','算法工程师');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z12','33000','架构师');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z13','53000','架构师');
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(null,'z13','153000','架构师');
Query OK, 1 row affected (0.00 sec)

mysql> update salary set name='z14' where pay_for_per_month=153000
    -> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from salary;
+----+------+-------------------+------------+
| id | name | pay_for_per_month | position   |
+----+------+-------------------+------------+
|  1 | z1   |             10000 | JAVA研发   |
|  2 | z2   |             11000 | JAVA研发   |
|  3 | z3   |             17000 | JAVA研发   |
|  4 | z4   |             18000 | c++研发    |
|  5 | z5   |              8000 | c++研发    |
|  6 | z6   |              6000 | 嵌入式开发 |
|  7 | z7   |              8000 | 嵌入式开发 |
|  8 | z8   |              9000 | 嵌入式开发 |
|  9 | z9   |             12000 | 算法工程师 |
| 10 | z10  |             15000 | 算法工程师 |
| 11 | z11  |             23000 | 算法工程师 |
| 12 | z12  |             33000 | 架构师     |
| 13 | z13  |             53000 | 架构师     |
| 14 | z14  |            153000 | 架构师     |
+----+------+-------------------+------------+
14 rows in set (0.00 sec)


mysql> select position,avg(pay_for_per_month) from salary group by position;
+------------+------------------------+
| position   | avg(pay_for_per_month) |
+------------+------------------------+
| JAVA研发   |             12666.6667 |
| c++研发    |             13000.0000 |
| 嵌入式开发 |              7666.6667 |
| 算法工程师 |             16666.6667 |
| 架构师     |             79666.6667 |
+------------+------------------------+
5 rows in set (0.00 sec)

mysql> select position,min(pay_for_per_month) from salary group by position;
+------------+------------------------+
| position   | min(pay_for_per_month) |
+------------+------------------------+
| JAVA研发   |                  10000 |
| c++研发    |                   8000 |
| 嵌入式开发 |                   6000 |
| 算法工程师 |                  12000 |
| 架构师     |                  33000 |
+------------+------------------------+
5 rows in set (0.00 sec)

mysql> select position,max(pay_for_per_month) from salary group by position;
+------------+------------------------+
| position   | max(pay_for_per_month) |
+------------+------------------------+
| JAVA研发   |                  17000 |
| c++研发    |                  18000 |
| 嵌入式开发 |                   9000 |
| 算法工程师 |                  23000 |
| 架构师     |                 153000 |
+------------+------------------------+
5 rows in set (0.00 sec)

mysql> select position,count(pay_for_per_month) from salary group by position;
+------------+--------------------------+
| position   | count(pay_for_per_month) |
+------------+--------------------------+
| JAVA研发   |                        3 |
| c++研发    |                        2 |
| 嵌入式开发 |                        3 |
| 算法工程师 |                        3 |
| 架构师     |                        3 |
+------------+--------------------------+
5 rows in set (0.00 sec)

总的俩说，要想where和 group by连用，where 必须写在group by的前面，having写在group by的后面