(05)oracle新人笔记------子查询（重点）

一、单行子查询：=   <   >    >=    <=     <>

1. SELECTename

FROM emp

WHERE sal>(SELECT sal

             FROM emp

             WHERE ename='JONES');

 

2. SELECTename,job

FROM emp

WHERE job=(SELECT job

           FROM emp

           WHERE empno=7369)

AND sal>(SELECT sal

         FROM emp

         WHERE empno=7876);

 

 

3. 子查询中使用组函数

SELECT ename,job,sal

FROM emp

WHERE sal=(SELECT MIN(sal)

             FROM emp);      

 

4. HAVING子句中的子查询

SELECT deptno,MIN(sal)

FROM emp

GROUP BY deptno

HAVING MIN(sal)>(SELECT MIN(sal)

                    FROM emp

                    WHERE 57 ptno=20);

 

 

查询哪个部门的员工人数高于平均人数

SELECT deptno,count(empno)

FROM emp

GROUP BY deptno

HAVING COUNT(empno)>(

                     SELECT AVG(COUNT(empno))

                     FROM emp

                     GROUP BY deptno); 

 

 

 

二、多行子查询   IN / ANY / ALL

IN

SELECT ename,sal

FROM emp

WHERE empno IN(SELECT mgr FROM emp);

 

 

ANY（或）

(1)  >ANY       (2)  

查询部门编号不为10，工资比10部门任意一名员工工资高的 编号、姓名.....

SELECT empno,ename,job,sal

FROM emp

WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10)

AND deptno<>10;

 

ALL(与)

查询部门编号不为10，工资比10部门所有员工工资高的 编号、姓名.....

SELECT empno,ename,job,sal

FROM emp

WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=10)

AND deptno<>10;

 

 

 

三、多列子查询

• SELECTename,deptno,job,hiredate

FROM emp

WHERE (deptno,job) IN 

       (SELECT deptno,job FROM emp WHERE       

        TO_CHAR(hiredate,'yyyy')='1981')

AND TO_CHAR(hiredate,'yyyy')<>'1981';

 

• 查询和1981年入职的任意一个员工的部门或职位相同员工姓名、部门、职位、入职日期，不包括1981入职员工。

SELECT ename,deptno,job,hiredate

FROM emp

WHERE deptno IN 

(SELECT deptno FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1981')

AND job IN 

(SELECT job FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1981')

AND TO_CHAR(hiredate,'YYYY')<>'1981';

 

四、在FROM子句中使用子查询

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

 

五、分页查询

1、ROWNUM介绍（就是方便查看的序号,实现分页）

SELECT  ROWNUM,ename,job,mgr from emp;

 

2、TOP-N查询

//就输出指定条记录

SELECT * FROM emp WHERE ROWNUM<=5;

 

3、ROWNUM分页

SELECT ename,hiredate

FROM emp

WHERE ROWNUM<=5 

ORDER BY hiredate ASC;

 

 

SELECT emp.ename,emp.hiredate

FROM emp,dept

WHERE ROWNUM<=2 AND dept.loc='CHICAGO'

ORDER BY hiredate ASC;

 

 

SELECT * FROM 

         (SELECT a.*,ROWNUM nu FROM 

                               (SELECT * FROM emp) a

          WHERE ROWNUM<=6)

WHERE nu>3;