SQL基础（一七）--- 数据分组

数据表信息:

mysql> select * from t_employee;
+----------+-------+------+---------+-------------+---------------+
| fnumber  | fname | fage | fsalary | fsubcompany | fdepartment   |
+----------+-------+------+---------+-------------+---------------+
| DEV001   | Tom   |   25 | 8300    | Beijing     | Development   |
| DEV002   | Jerry |   28 | 2300.8  | ShenZhen    | Development   |
| HR001    | Jane  |   23 | 2200.88 | Beijing     | HumanResource |
| HR002    | Tina  |   25 | 5200.36 | Beijing     | HumanResource |
| IT001    | Smith |   28 | 3900    | Beijing     | InfoTech      |
| IT002    | NULL  |   27 | 2800    | ShenZhen    | InfoTech      |
| SALES001 | John  |   23 | 5000    | Beijing     | Sales         |
| SALES002 | Kerry |   28 | 6200    | Beijing     | Sales         |
| SALES003 | Stone |   22 | 1200    | ShenZhen    | Sales         |
+----------+-------+------+---------+-------------+---------------+
9 rows in set

按照年龄分组：

mysql> select fage from t_employee
    -> group by fage;
+------+
| fage |
+------+
|   22 |
|   23 |
|   25 |
|   27 |
|   28 |
+------+
5 rows in set

将fsubcompany字段值为“BeiJing”的员工按照年龄分组：

mysql> select fage from t_employee
    -> where fsubcompany='BeiJing'
    -> group by fage;
+------+
| fage |
+------+
|   23 |
|   25 |
|   28 |
+------+
3 rows in set

获取每个年龄的平均工资：

mysql> select fage,avg(fsalary) from t_employee
    -> group by fage;
+------+--------------+
| fage | avg(fsalary) |
+------+--------------+
|   22 | 1200.000000  |
|   23 | 3600.440000  |
|   25 | 6750.180000  |
|   27 | 2800.000000  |
|   28 | 4133.600000  |
+------+--------------+
5 rows in set

分别按照分公司和部门分组 ：

mysql> select fsubcompany, fdepartment from t_employee
    -> group by fsubcompany,fdepartment;
+-------------+---------------+
| fsubcompany | fdepartment   |
+-------------+---------------+
| Beijing     | Development   |
| Beijing     | HumanResource |
| Beijing     | InfoTech      |
| Beijing     | Sales         |
| ShenZhen    | Development   |
| ShenZhen    | InfoTech      |
| ShenZhen    | Sales         |
+-------------+---------------+
7 rows in set