dept表deptno,deptname
emp表empno,empname,salary,deptno
先通过查询得到平均工资视图,然后在联结查询平均工资表和员工表,Group by修饰count(*),Group by要在where子句之后,order by子句之前。
select a.*,avgsalfrom emp as a,(select deptno,avg(salary) as avgsal from emp group by deptno) as bwhere a.deptno=b.deptno and a.salary>b.avgsalorder by a.deptno;
高于平均工资的人数
select a.deptno,avgsal,count(*) as numberfrom emp as a,(select deptno,avg(salary) as avgsal from emp group by deptno) as bwhere a.deptno=b.deptno and a.salary>b.avgsalgroup by a.deptnoorder by a.deptno;