emp(员工表)
(
id number pk,
name char ,
salary number,
depid number references dep(id),
)dep(部门表)
(
id number pk,
name char ,
manager varchar2(10)
)
--------------------------------------------------
查询出所有工资高于所在部门平均工资的职工信息
(员工号,姓名,工资,部门名称,所在部门平均工资)。
(
id number pk,
name char ,
salary number,
depid number references dep(id),
)dep(部门表)
(
id number pk,
name char ,
manager varchar2(10)
)
--------------------------------------------------
查询出所有工资高于所在部门平均工资的职工信息
(员工号,姓名,工资,部门名称,所在部门平均工资)。
(select a.dep.id,avg(emp.salary) avg_s from emp a,dep b where a.depid=b.id
group by a.dep.id) t
where emp.depid=dep.id and dep.id=t.id and emp.salary >t.avg_s
2 2部门 22 emp表数据
1 11 10 1
2 22 20 1
3 33 30 1
4 44 40 2
5 55 50 2
6 66 60 2 sql执行结果数据
3 33 30 1部门 20
6 66 60 2部门 50
sql 语句: select
a.id,a.name,a.salary,b.name,c.tt as avgsalary
from emp a,dep b,(select avg(salary) as tt,depid from emp c
group by depid) c
where a.depid = b.id
and c.depid = b.id
having a.salary > c.tt
group by a.id,a.name,a.salary,c.tt,b.name