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)
)
--------------------------------------------------
查询出所有工资高于所在部门平均工资的职工信息
(员工号,姓名,工资,部门名称,所在部门平均工资)。
解决方案 »
- oracle如何导出system用户下创建的表,而不导出系统表
- 一个PL/SQL问题
- 如何从SQL的SELECT语句中检索出表名
- 请教在数据库中写入标点符号的问题,急!!!
- 新年到了,散分,祝大家猴年走大财,发大运!
- 求一个带有执行动态SQL+异常处理的存储过程.
- oracle report的问题,救命呀!
- developer/2000是否就是oracle网站上的Oracle Developer Suite for Windows ?
- 求助 Oracle 连接的问题!
- plsql使用Oracle11g 64位导出dmp文件闪一下 但是没导出来结果,如何解决?
- ORACLE为什么有的时候登录不上去?
- 如何实现在ORACLE FORM中特定的信息拥有特定的背景色??
(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部门 22emp表数据
1 11 10 1
2 22 20 1
3 33 30 1
4 44 40 2
5 55 50 2
6 66 60 2sql执行结果数据
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
教一下..........
谢过
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
thanks.......
楼主 结贴了吧
俺等着分 问问题啊
FROM EMP E,(SELECT DEPTNO,AVG(SALARY) AVGSAL FROM EMP GROUP BY DEPID) TEMPEMP
WHERE E.SALARY>TEMPEMP.AVGSAL AND E.DEPID=TEMPEMP.DEPID;
from emp a,
(select depid, avg(salary) avg_salary from emp group by depid) b,
dep c where a.salary > b.avg_salary
and a.depid = b.depid
and c.depid(+)=b.depid
1 select a.empno,a.ename,a.job,a.sal,b.dname,d.avgsal
2 from emp a, dept b,
3 ( select deptno,avg(sal) avgsal
4 from emp c
5 group by deptno
6 ) d
7 where a.deptno = b.deptno
8 and a.deptno = d.deptno
9* and a.sal > d.avgsal
SQL> / EMPNO ENAME JOB SAL DNAME AVGSAL
---------- ---------- --------- ---------- -------------- ----------
7839 KING PRESIDENT 5000 ACCOUNTING 2916.66667
7902 FORD ANALYST 3000 RESEARCH 2175
7788 SCOTT ANALYST 3000 RESEARCH 2175
7566 JONES MANAGER 2975 RESEARCH 2175
7499 ALLEN SALESMAN 1600 SALES 1566.66667
7698 BLAKE MANAGER 2850 SALES 1566.66667