Oracle×Ó²éѯ

×Ó²éѯ
 µ¥ÐÐ×Ó²éѯ(single-row subqueries)
    Ê¹ÓõÄÔËËã·ûºÅ(=,>,<,>=,<=,<>)
  ¶àÐÐ×Ó²éѯ(multiple-row subqueries)
    Ê¹ÓõÄÔËËã·ûºÅ(in,not in,exists,not exits,all,any)
   Ïà¹Ø×Ó²éѯ(correlated subqueries)
    ¸ñʽ select ÁÐÃû,(select Óï¾ä) from ±íÃû
   ±êÁ¿×Ó²éѯ(scalar subqueries)
    ×Ó²éѯÊÇ·µ»Øµ¥Ðе¥ÁÐ,¸ñʽͬÉÏ
   ¶àÁÐ×Ó²éѯ(multiple-column subqueries)
   ÔÚDDLÓï¾äÖÐʹÓÃ×Ó²éѯ
   ÔÚDMLÓï¾äÖÐʹÓÃ×Ó²éѯ
--------
µ¥ÐÐ×Ó²éѯ
 --ÏÔʾ¹¤×Ê×î¸ßµÄ¹ÍÔ±ÐÅÏ¢
 Select ename,deptno,sal from emp
 Where sal=(select max(sal) from emp);
--------
¶àÐÐ×Ó²éѯ
 --ÏÔʾÓ벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
 Select ename,deptno,sal,job from emp
 Where job in (select distinct job from emp where deptno=20);
 --ÏÔʾ²»Ó벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
 Select ename,deptno,sal,job from emp where job not in (select distinct job from emp where deptno=20);
 --ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄËùÓйÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
 select ename,deptno,sal ,job from emp
 where sal>all(select sal from emp where deptno=20);
 --ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄÈκιÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
 select ename,deptno,sal ,job from emp
 where sal>any(select sal from emp where deptno=20); 
---------
Ïà¹Ø×Ó²éѯ
 --ÏÔʾÿ¸ö²¿ÃŵÄ×î¸ß¹¤×ʵĹÍÔ±ÐÅÏ¢
 select deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
 from emp a order by deptno;
 --Ôö¼Ódistinct
 select distinct deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
 from emp a order by deptno;
 --ÏÔʾ¹¤×÷ÔÚNEW YORKµÄ¹ÍÔ±ÐÅÏ¢
 select ename,deptno,sal,job from emp
 where exists (select 'x' from dept where dept.deptno=emp.deptno and dept.loc='NEW YORK');
---------
±êÁ¿×Ó²éѯ
 --·µ»Øµ¥Ðе¥ÁÐ
 Select count(*) from emp;
 Select sum(sal)