06. SQL 基础--> 子查询

7369 FROM dual UNION ALL SELECT NULL FROM dual ); 2 3 4 5 6 7 no rows selected idle> idle> SELECT * FROM emp e WHERE e.empno IN (‘7369‘,NULL); 2 3 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20

 

注：子查询要包含在括号内

子查询一般放在比较条件的右侧

除非进行TOP－N 分析，否则不要在子查询中使用ORDER BY。 */

 

/*

八、多列子查询

1、成对比较

查询工资为部门最高的记录 

idle> select * from scott.emp
where (sal,job) in        
(select max(sal),job from scott.emp group by job);
  2    3  
     EMPNO ENAME      JOB	       MGR HIREDATE	     SAL       COMM	DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK	      7782 1982-01-23	    1300		    10
      7499 ALLEN      SALESMAN	      7698 1981-02-20	    1600	300	    30
      7839 KING       PRESIDENT 	   1981-11-17	    5000		    10
      7566 JONES      MANAGER	      7839 1981-04-02	    2975		    20
      7902 FORD       ANALYST	      7566 1981-12-03	    3000		    20
      7788 SCOTT      ANALYST	      7566 1987-04-19	    3000		    20

6 rows selected.

　　

2、非成对比较,实现了与上述类似的功能

idle> select * from scott.emp
where sal in (select max(sal) from scott.emp group by job)
and job in (select distinct job from scott.emp);
  2    3  
     EMPNO ENAME      JOB	       MGR HIREDATE	     SAL       COMM	DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK	      7782 1982-01-23	    1300		    10
      7499 ALLEN      SALESMAN	      7698 1981-02-20	    1600	300	    30
      7566 JONES      MANAGER	      7839 1981-04-02	    2975		    20
      7788 SCOTT      ANALYST	      7566 1987-04-19	    3000		    20
      7902 FORD       ANALYST	      7566 1981-12-03	    3000		    20
      7839 KING       PRESIDENT 	   1981-11-17	    5000		    10

　　

九、嵌套子查询

即位于子查询内部的子查询，嵌套层数最多可达层。然而应尽量避免使用嵌套子查询，使用表连接的查询性能会更高

idle> select deptno,Num_emp
from (select deptno,count(empno) as Num_emp from emp group by deptno) d
where Num_emp > 3;
  2    3  
    DEPTNO    NUM_EMP
---------- ----------
	30	    6
	20	    5

注意：子查询对空值的处理
除了count(*)外，都会忽略掉空值　　

 

06. SQL 基础--> 子查询

标签：实现   iss   分组   highlight   sub   功能   tno   返回   结果