题目:显示部门工资怎和高于雇员工资怎和三分之一的部门名及工资怎和。
第一种方法:
SQL> select dname,sum(sal) as dept_total from emp,dept
2 where emp.deptno=dept.deptno group by dname
3 having sum(sal)>
4 (select sum(sal)*1/3 from emp,dept
5 where emp.deptno=dept.deptno);DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 13075
问题:我就是不理解我在主查询中的sum(sal)是部门工资怎和?我理解为每个部门工资怎和。请高手帮我化解一下.谢谢第二种方法:
SQL> with summary as (
2 select dname,sum(sal) as dept_total from emp,dept
3 where emp.deptno=dept.deptno group by dname)
4 select dname,dept_total from summary where dept_total>
5 (select sum(dept_total)*1/3 from summary);DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 13075
雇员工资怎和是不是就是每个部门工资怎和的和?我想应该是的。不过前提条件是每个雇员都分配在相应的部门工作。
select dname,dept_total from
( select deptno,sum(sal) as dept_total from emp group by deptno
having sum(sal)>(select sum from total)
)s, dept where dept.deptno = s.deptno个人认为,统计员工工资时不要关联部门