1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

mysql> select post,group_concat(name),count(id) from employee group by post;+-----------+-------------------------------------------------+-----------+| post      | group_concat(name)                              | count(id) |+-----------+-------------------------------------------------+-----------+| operation | 程咬铁,程咬铜,程咬银,程咬金,张野                |         5 || sale      | 格格,星星,丁丁,丫丫,歪歪                        |         5 || teacher   | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,alex   |         6 |+-----------+-------------------------------------------------+-----------+3 rows in set (0.00 sec)mysql> select post,group_concat(name),count(id) from employee group by post having count(id) <2 ;Empty set (0.05 sec)

 

3. 查询各岗位平均薪资大于10000的岗位名、平均工资

mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;+-----------+---------------+| post      | avg(salary)   |+-----------+---------------+| operation |  16800.026000 || teacher   | 175766.718333 |+-----------+---------------+2 rows in set (0.04 sec)

 

4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

mysql> select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;+-----------+--------------+| post      | avg(salary)  |+-----------+--------------+| operation | 16800.026000 |+-----------+--------------+1 row in set (0.00 sec)