1. 查询岗位名以及岗位包含的所有员工名字
mysql> select post,group_concat(name) from employee group by post;
+-----------+-------------------------------------------------+
| post | group_concat(name) |
+-----------+-------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| sale | 格格,星星,丁丁,丫丫,歪歪 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,alex |
+-----------+-------------------------------------------------+
3 rows in set (0.00 sec)
2. 查询岗位名以及各岗位内包含的员工个数
mysql> select post,count(id) from employee group by post;
+-----------+-----------+
| post | count(id) |
+-----------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 6 |
+-----------+-----------+
3 rows in set (0.00 sec)
3. 查询公司内男员工和女员工的个数
mysql> select sex,count(id) from employee group by sex;
+--------+-----------+
| sex | count(id) |
+--------+-----------+
| male | 8 |
| female | 8 |
+--------+-----------+
2 rows in set (0.00 sec)
4. 查询岗位名以及各岗位的平均薪资
mysql> select post,avg(salary) from employee group by post;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 175766.718333 |
+-----------+---------------+
3 rows in set (0.00 sec)
5. 查询岗位名以及各岗位的最高薪资
mysql> select post,max(salary) from employee group by post;
+-----------+-------------+
| post | max(salary) |
+-----------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
+-----------+-------------+
3 rows in set (0.00 sec)
6. 查询岗位名以及各岗位的最低薪资
mysql> select post,min(salary) from employee group by post;
+-----------+-------------+
| post | min(salary) |
+-----------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
+-----------+-------------+
3 rows in set (0.00 sec)
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 136700.055000 |
| female | 7250.183750 |
+--------+---------------+
2 rows in set (0.00 sec)
8. 查询岗位名以及各岗位内 年龄50岁以上 包含的员工个数
先把年龄大于50岁的 先取出来 然后进行分组
mysql> select post,count(id) from employee where age >50 group by post;
+---------+-----------+
| post | count(id) |
+---------+-----------+
| teacher | 2 |
+---------+-----------+
1 row in set (0.00 sec)
