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的后面
