查询出各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序
程序员文章站
2022-07-07 09:46:22
...
1,创建表格 emp
2.思路:
(1)首先查询各个部门的平均工资
select detpid, avg(salary) as avgsalary
from emp
group by detpid
(2)利用联合查询的思想进行查询:select * from table1,table2 where table1.name=table2.name
即把emp表与上表查询出的结果进行联合查询,找出所有工资大于平均工资的记录。
select e.* from emp as e,
(select detpid,avg(salary) as avgsalary from emp group by detpid) as b
where e.detpid=b.detpid and e.salary>b.avgsalary ;
(3)查询出各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序
1 select e.deptid,count(*) from emp as e,
2 (select detpid,avg(salary) as avgsalary from emp group by detpid) as b
3 where e.detpid=b.detpid and e.salary>b.avgsalary
4 group by e.deptid
5 order by e.deptid;
自己遇到的问题
1就是联合查询时“主表”没有分组,就是没有上面代码第四行,这时代码会报错。
1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sql.e.detpid'; this is incompatible with sql_mode=only_full_group_by
2 select 后面字段中间要用 逗号 分隔开,最后一个字段 也就是from前字段不能有逗号