Oracle 子查询

• ROWNUM 
     –ROWNUM是一个伪列，伪列是类似于表中的列，而 
        实际并没有存储在表中的特殊列； 
     –ROWNUM的功能是在每次查询时，返回结果集的顺序号， 
        这个顺序号是在记录输出时才一步一步产生的，第一行 
        显示为1，第二行为2，以此类推。 
 • ROWNUM 
ROWNUM使用的注意点： 
• 1.如下SQL语句，SELECT * FROM EMP WHERE ROWNUM>2;查询 
不到任何记录，因为ROWNUM是在记录输出时才生成，且总是 
从1开始，所以输出的第一条记录不满足>2的条件，被过滤掉， 
第二条的ROWNUM又成了1，又不满足>2的条件，又被过滤掉， 
依此类推，所以永远没有满足条件的记录，返回为空。所以 
对于ROWNUM只能执行<、<=运算，不能执行>、>=或一个区间 
运算Between..And等 
•2.ROWNUM和ORDER BY一起使用时，因为ROWNUM在记录输出时 
生成，而ORDER BY子句在最后执行，所以当两者一起使用时， 
需要注意ROWNUM实际是已经被排了序的ROWNUM


• TOP-N查询 
     –Top-N查询主要是实现表中按照某个列排序，输出最大或 
       最小的N条记录功能。 
 •Top-N分析语法： 
 
   SELECT [列名], ROWNUM 
   FROM      (SELECT [列名] 
               FROM 表名 
               ORDER  BY Top-N操作的列 ASC|DESC) 
   WHERE  ROWNUM <=  N; 
 
         •ASC:查询最小的N条记录 
         •DESC：查询最大的N条记录


分页 
 • 分页查询 
      –在Oracle中，利用ROWNUM的特性，可以实现数据库端的分 
        页查询，查询语法为： 
      –1.当未指定需要按照某列排序，语法为： 
 
SELECT b.* 
FROM (SELECT ROWNUM rn,[ 1, 2,....n] 
                      列名 列名  列名 
        FROM  1,[  2,...  n] 
            表名  表名   表名 
        WHERE [AND] ROWNUM <=目标页数*每页记录数) b   
WHERE rn > (目标页数-1)*每页记录数 
--
或 （一般用下面这种模式）
SELECT b.* 
FROM (SELECT ROWNUM rn,[ 1, 2,....n] 
                      列名 列名  列名 
        FROM  1,[  2,...  n] 
            表名  表名   表名 
         [WHERE 条件表达式]) b 
WHERE rn <= i*j      and rn > (i-1)*j
        i为第i页，j为每页的行数




第七章
练习一
1.查询入职日期最早的员工姓名，入职日期
select e.ename,e.hiredate
from emp e 
WHERE e.hiredate in (select min(HIREDATE) from emp group by e.ename);


2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
-- 下面是查询测试
-- select e.empno,e.ename, e.job,sal,e.deptno,dname,loc
-- from emp e ,dept d
-- where e.deptno = d.deptno
-- and d.loc='CHICAGO';
下面是结果：
select e.ename,e.sal,d.dname,d.loc
from emp e,DEPT d
where e.deptno = d.deptno
       and e.sal> any (select sal from emp where ename='SMITH')
       and d.loc ='CHICAGO'
order by e.sal asc;


3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
select e.ename,e.hiredate,e.deptno
from emp e
where e.hiredate < all (select HIREDATE from emp where deptno=20);


4.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数
select d.deptno,d.dname,count(e.ename)
from emp e , dept d
where d.DEPTNO = e.DEPTNO
group by d.DEPTNO,d.DNAME 
HAVING (count(e.ename)>
       (select count(ename)/count(DISTINCT deptno) from emp));


第七章
练习二
1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ENAME ,HIREDATE
FROM EMP 
WHERE HIREDATE < ANY (SELECT HIREDATE FROM EMP where DEPTNO=10) 
      AND DEPTNO <> 10 ;


2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ENAME ,HIREDATE
FROM EMP 
WHERE HIREDATE < ALL (SELECT HIREDATE FROM EMP where DEPTNO=10) 
      AND DEPTNO <> 10 ;  
   
-- 查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
SELECT ENAME , JOB , DEPTNO
FROM EMP
WHERE  JOB = ANY (SELECT JOB FROM EMP WHERE DEPTNO =10)
       AND DEPTNO <> 10;


练习三
1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，
不包括10部门员工
select m.ename, m.job,m.mgr,m.DEPTNO
from emp m
where  (m.job,m.mgr) in (select e.job,e.mgr from emp e where e.deptno=10)
      and m.DEPTNO<>10;
-- in 可以换成 = any


2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，
不包括10部门员工
select m.ename, m.job,m.mgr,m.DEPTNO
from emp m
where  (m.job in (select e.job from emp e where e.deptno=10) and m.DEPTNO<>10)
    or (m.mgr in (select e.mgr from emp e where e.deptno=10) and m.DEPTNO<>10);


练习四：
1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资
SELECT
E.ename,E.JOB,D.dname,b.salavg 职位平均工资,e.sal 个人工资
FROM emp E ,(SELECT JOB,AVG(sal) salavg FROM emp GROUP BY emp.JOB)b,dept d
WHERE
  e.DEPTNO = d.DEPTNO
  and E.sal > b.salavg
  and e.job = b.job
order by 2;


1.1 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
SELECT  a.ename, a.sal, a.deptno, b.salavg 
FROM       emp a, (SELECT        deptno, avg(sal) salavg 
FROM        emp 
GROUP BY deptno) b 
WHERE      a.deptno = b.deptno 
AND        a.sal > b.salavg; 




2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOTT和BLANK本人
条件：select job,mgr from emp where ename='SCOTT' or ename = 'BLAKE';
select e.ename,e.job 
from emp e, (select job,mgr from emp where ename='SCOTT' or ename = 'BLAKE') b
where e.job=b.job and e.mgr = b.mgr
      and e.ename not in('SCOTT','BLAKE');


3.查询不是经理的员工姓名
SELECT  DISTINCT mgr FROM emp;
select ename
from emp 
where empno not in (select distinct mgr from emp where mgr is not null);


练习五
1.查询入职日期最早的前5名员工姓名、入职日期。
select rownum, ename,hiredate from emp where rownum <=5;


2.查询工作在CHIACAGO并且入职日期最早的前两名员工姓名，入职日期
select rownum,e.ename,hiredate 
from emp e join dept d on e.DEPTNO = d.DEPTNO 
where d.loc ='CHICAGO' and rownum<3;


练习六
1.按照每页显示5条记录，分别查询第1页，第2 页，第3页信息，
  要求显示员工姓名、入职日期 、部门名称。
select b.*
from (SELECT rownum rn,e.ENAME ,e.hiredate,d.DNAME
FROM  EMP e ,DEPT d
WHERE e.DEPTNO = d.DEPTNO
)b
WHERE rn <=1*5 AND rn >(1-1)*5;


select b.*
from (SELECT rownum rn,e.ENAME ,e.hiredate,d.DNAME
FROM  EMP e ,DEPT d
WHERE e.DEPTNO = d.DEPTNO
)b
WHERE rn <=2*5 AND rn >(2-1)*5;


select b.*
from (SELECT rownum rn,e.ENAME ,e.hiredate,d.DNAME
FROM  EMP e ,DEPT d
WHERE e.DEPTNO = d.DEPTNO
)b
WHERE rn <=3*5 AND rn >(3-1)*5;


课后作业
1.查询工资高于编号为7782的员工工资，并且和7369号员工
   从事相同工作的员工的编号、姓名及工资 
SELECT  EMPNO ,ENAME ,SAL
FROM EMP
WHERE (sal > (SELECT SAL from EMP WHERE EMPNO = 7782))
      AND (job = (SELECT job FROM EMP WHERE EMPNO = 7369));


--2.查询工资最高的员工姓名和工资
SELECT ENAME , sal from EMP WHERE SAL =(SELECT max(sal) FROM EMP );


3. 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资
select e.DEPTNO,d.dname,min(sal)
from emp e join dept d on e.DEPTNO = d.DEPTNO
group by e.DEPTNO,d.DNAME
HAVING (min(sal)>(select min(sal) from emp where deptno = 10));
 
4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
SELECT EMPNO,ENAME,SAL
FROM EMP E1
WHERE SAL=(SELECT MIN(SAL) FROM EMP E2 WHERE DEPTNO=E1.DEPTNO);


SELECT EMPNO,ENAME,SAL
FROM EMP 
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MIN(SAL) FROM EMP GROUP BY DEPTNO);


5.显示经理是KING的员工姓名，工资。
SELECT ENAME,SAL FROM EMP
WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='KING');


6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
SELECT ENAME,SAL,HIREDATE FROM EMP
WHERE HIREDATE>(SELECT HIREDATE FROM EMP WHERE ENAME='SMITH');


7.使用子查询的方式查询哪些职员在NEW YORK工作。
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE LOC='NEW YORK';
SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');


8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
SELECT ENAME,HIREDATE FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH') AND ENAME<>'SMITH';


9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL>(SELECT AVG(SAL) FROM EMP)


10.写一个查询显示其上级领导是King的员工姓名、工资。
SELECT ENAME,SAL FROM EMP
WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='KING');


11.显示所有工作在RESEARCH部门的员工姓名，职位。
SELECT ENAME,JOB 
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='RESEARCH');


12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。
SELECT DEPTNO,AVG(SAL) 
FROM EMP 
GROUP BY DEPTNO 
HAVING(AVG(SAL)>(
SELECT AVG(SAL) 
FROM EMP 
WHERE DEPTNO=20)
);


13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
SELECT ENAME,SAL,ASAL,SAL-ASAL FROM EMP INNER JOIN
(SELECT DEPTNO,AVG(SAL) ASAL FROM EMP GROUP BY DEPTNO) AA ON EMP.DEPTNO=AA.DEPTNO WHERE SAL>ASAL;


SELECT ENAME SAL,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E2.DEPTNO),SAL-(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E2.DEPTNO)
FROM EMP E2 WHERE sal>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E2.DEPTNO);


14. 列出至少有一个雇员的所有部门
SELECT * FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING(COUNT(*)>0));
SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO from EMP GROUP BY DEPTNO HAVING count(DEPTNO)>0);




15. 列出薪金比"SMITH"多的所有雇员
SELECT * FROM EMP e1 WHERE E1.SAL >(SELECT e2.sal from EMP e2 WHERE e2.ename='SMITH');
SELECT * from emp where sal>(select sal from emp where Ename='SMITH');


16. 列出入职日期早于其直接上级的所有雇员
SELECT * FROM EMP e1 WHERE HIREDATE <(SELECT e2.hiredate FROM emp e2 WHERE E1.MGR = E2.Empno);


SELECT * FROM EMP E1 WHERE HIREDATE <(SELECT HIREDATE FROM EMP WHERE EMPNO=E1.MGR);


17. 找员工姓名和直接上级的名字
SELECT e1.ename ,e2.ename FROM emp e1 ,emp e2 WHERE E1.MGR = E2.EMPNO(+);


--SELECT E1.ENAME,E2.ENAME FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR=E2.EMPNO;
SELECT ENAME,(SELECT ENAME FROM EMP WHERE EMPNO=E1.MGR) FROM EMP E1;


18. 显示部门名称和人数
SELECT DNAME,(SELECT COUNT(*) FROM EMP WHERE DEPTNO=D1.DEPTNO) FROM DEPT D1;
SELECT DNAME,COUNT(EMPNO) FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO GROUP BY DNAME;


19. 显示每个部门的最高工资的员工
SELECT * FROM EMP WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO);
SELECT * from emp e1 where sal=(select max(sal) from emp e2 where e2.deptno=e1.deptno);


20. 显示出和员工号7369部门相同的员工姓名，工资
21. 显示出和姓名中包含"W"的员工相同部门的员工姓名
22. 显示出工资大于平均工资的员工姓名，工资
23. 显示出工资大于本部门平均工资的员工姓名，工资
24. 显示每位经理管理员工的最低工资，及最低工资者的姓名
select ename,sal from emp where (mgr,sal) in(select mgr,min(sal) from emp group by mgr);


25. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
SELECT ENAME,HIREDATE FROM EMP 
WHERE HIREDATE>(SELECT hiredate FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP))


26. 显示出平均工资最高的的部门平均工资及部门名称

--SELECT * FROM (SELECT DNAME,AVG(SAL) FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO GROUP BY DNAME ORDER BY AVG(SAL) DESC) WHERE ROWNUM <=1