EMP员工信息表,包含部门号DEPTNO,员工名ENAME,工资SAL。
DEPT部门信息表,包含部门信息号DEPTNO一列。 1、一部门为单位,找出每个部门员工工资发放总数,没有职工的部门也要列出。 2、找出工资超过其所在部门人均工资的所有员工的信息。
DEPT部门信息表,包含部门信息号DEPTNO一列。 1、一部门为单位,找出每个部门员工工资发放总数,没有职工的部门也要列出。 2、找出工资超过其所在部门人均工资的所有员工的信息。
select t1.deptno, sum(nvl(sal, 0)) total
from emp t1, dept t2
where t1.deptno(+) = t2.deptno
group by t1.deptno
order by t1.deptno
2.
select t3.deptno, t3.ename, t3.sal
from (select t1.deptno,
t1.ename,
case
when nvl(t1.sal, 0) >
(select avg(nvl(t2.sal, 0))
from emp t2
where t1.deptno = t2.deptno) then
sal
else
null
end sal
from emp t1
order by t1.deptno) t3
where t3.sal is not null
这个能简洁点
select t1.deptno, t1.ename, t1.sal
from emp t1,
(select deptno, avg(sal) sal from emp group by deptno order by deptno) t2
where t1.deptno = t2.deptno
and t1.sal > t2.sal
1.select d.depname, sum(nvl(sal,0)) from emp e , dept d where e.deptno = d.deptno group by deptno2.select e1.deptno,31.ename,t.sal
from emp e1 ,(select e2.deptno,avg(e2.sal) salay from emp e2 group by deptno)
where e1.deptno = e2.deptno
and e1.sal > e2.salay
from emp t1, dept t2
where t1.deptno = t2.deptno(+)
group by t1.deptno;2.select t1.deptno,t1.ename,t1.sal
from emp t1
where sal>(select avg(sal) from emp t2 where t1.deptno=t2.deptno)
;
from emp t1
where sal>(select avg(sal) from emp t1 where t1.deptno=t2.deptno)
;
select t2.DEPTNO,t2.ENAME,t2.SAL from (select avg(sal) c1 from emp group by deptno) t1,emp t2 where t1.depton=t2.deptno
and t2.sal>t1.c1