oracle子查詢

標籤：子查詢

--公司平均工資  單行單列

--select avg(sal) from emp;

--找到高於此平均工資的僱員姓名、職位、工資

--select e.ename,e.job,e.sal

--from emp e

--where e.sal >(select avg(sal) from emp);

--領導姓名

--select e.ename,e.job,e.sal,m.ename,m.job

--from emp e,emp m，dept d

--where e.sal >(select avg(sal) from emp)

--and e.mgr=m.empno(+) and e.deptno=d.deptno;

--部門名稱

--select e.ename,e.job,e.sal,m.ename,m.job,d.dname

--from emp e,emp m,dept d

--where e.sal >(select avg(sal) from emp)

--and e.mgr=m.empno(+) and e.deptno=d.deptno;

--部門人數

select e.ename,e.job,e.sal,m.ename,m.job,d.dname,dtemp.count,s.grade,stemp.cou

from emp e,emp m,dept d,(select deptno dno,count(empno) count from emp group by deptno)dtemp,salgrade s,(select s1.grade sg,count(e1.empno) cou

from emp e1,salgrade s1

where e1.sal between s1.losal and s1.hisal

group by s1.grade) stemp

where e.sal >(select avg(sal) from emp)

and e.mgr=m.empno(+) and e.deptno=d.deptno

and dtemp.dno=d.deptno

and e.sal between s.losal and s.hisal

and stemp.sg=s.grade;

本文出自 “10281302” 部落格，請務必保留此出處http://10291302.blog.51cto.com/10281302/1695268

oracle子查詢