Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Oracle ¶à±íÁ¬½Ó×Ó²éѯ

1.Çó²¿ÃÅÖÐÄÄЩÈËнˮ×î¸ß£º
select ename,sal
from emp join
(
    select max(sal) max_sal, deptno
    from emp
    group by deptno
) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
2.Çó²¿ÃÅÆ½¾ùнˮµÄµÈ¼¶£º
select deptno, avg_sal, grade
from
(
    select deptno, avg(sal) avg_sal
    from emp
    group by deptno
) t join salgrade s
on (t.avg_sal between s.losal and s.hisal);
3.Çó²¿ÃÅÆ½¾ùµÄнˮµÈ¼¶£º
select deptno, avg(grade)
from (
    select deptno, ename, grade
    from emp join salgrade s
    on emp.sal between s.losal and s.hisal
) t
group by deptno;
4.ÇóÄÄЩÈËÊǾ­ÀíÈË£º
select ename from emp where empno in (select distinct mgr from emp);
5.²»×¼Óþۼ¯º¯Êý£¬ÇóнˮµÄ×î¸ßÖµ£º
select distinct sal
from emp
where sal not in
(
    select distinct e1.sal
    from emp e1 join emp e2 on (e1.sal < e2.sal)
);
6.Ç󯽾ùнˮ×î¸ß²¿ÃŵIJ¿ÃűàºÅ£º
select deptno, avg_sal
from (
    select deptno, avg(sal) avg_sal
    from emp
    group by deptno
) where avg_sal = (
    select max(avg_sal)
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    )
);
7.Ç󯽾ùнˮ×î¸ß²¿ÃŵIJ¿ÃÅÃû³Æ£º
select deptno,dname
from dept
where deptno = (
    select deptno
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    ) where avg_sal = (
        select max(avg_sal)
     


Ïà¹ØÎĵµ£º

oracleÖÐdual±íµÄÓ÷¨

dualÊÇÒ»¸öÐéÄâ±í£¬ÓÃÀ´¹¹³ÉselectµÄÓï·¨¹æÔò£¬oracle±£Ö¤dualÀïÃæÓÀÔ¶Ö»ÓÐÒ»Ìõ¼Ç¼¡£ÎÒÃÇ¿ÉÒÔÓÃËüÀ´×öºÜ¶àÊÂÇ飬ÈçÏ£º
1¡¢²é¿´µ±Ç°Óû§£¬¿ÉÒÔÔÚ SQL PlusÖÐÖ´ÐÐÏÂÃæÓï¾ä select user from dual;
2¡¢ÓÃÀ´µ÷ÓÃϵͳº¯Êý
    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--»ñµÃµ±Ç°ÏµÍ³Ê±¼ ......

¹ØÓÚORACLEÍâÁ¬½Ó£¨+£©

ÏÖÔÚÓÐÒÔÏÂÁ½ÕÅ±í£º
                                              ±íA  ......

ѧϰ¡¶Oracle 9i10g±à³ÌÒÕÊõ¡·µÄ±Ê¼Ç (Æß) Îļþ

 ÕâÒ»ÕÂÖУ¬ÎÒÃǽ«·ÖÎö¹¹³ÉÊý¾Ý¿âºÍʵÀýµÄ8 ÖÖÎļþÀàÐÍ¡£
ÓëʵÀýÏà¹ØµÄÎļþÖ»ÓУº
²ÎÊýÎļþ£¨parameter file£©£ºÕâЩÎļþ¸æËßOracle ʵÀýÔÚÄÄÀï¿ÉÒÔÕÒµ½¿ØÖÆÎļþ£¬²¢ÇÒÖ¸
¶¨Ä³Ð©³õʼ»¯²ÎÊý£¬ÕâЩ²ÎÊý¶¨ÒåÁËijÖÖÄÚ´æ½á¹¹Óжà´óµÈÉèÖá£ÎÒÃÇ»¹»á½éÉÜ´æ´¢Êý¾Ý¿â²Î
ÊýÎļþµÄÁ½ÖÖÑ¡Ôñ¡£
¸ú×ÙÎļþ£¨trace file£ ......

OracleÖÐͳ¼ÆÆõºÏijÁÐÌõ¼þµÄÁÐ×ܺÍ

 ×î½üÔÚÏîÄ¿±¨±íÖÐÐèÒªÒ»¸ö²éѯÓï¾ä£¬ÓÃÀ´Í³¼Æ·ûºÏijһÁÐÌõ¼þµÄÆäËü¼¸ÁеĸöÊý
    ±ÈÈçÓÐÏÂÃæÒ»¸ö±í½á¹¹£º
   
    ÐèÒªÔÚÁÐDºóÃæÔö¼ÓÒ»ÁУ¬Í³¼ÆÔÚÁÐA²»Îª¿Õ£¬²¢ÇÒÁÐB¡¢C¡¢D²»Îª¿ÕµÄ¸öÊý
    ¾­¹ý×Ô¼ºÊÔÑ飬²éÕÒ°ïÖú£¬×ÜËãʵÏÖÁËÉÏÃæµÄ²éѯ¡£
&nb ......

OracleËæ»úº¯ÊýµÄÓ÷¨

 ÈÝÒ×µÃ˵£¬¾­¹ýdbms_random°üµ÷ÓÃËæ»úÊýµÄ²½Öè´óÌåÓÐ4ÖÖ£º
Ò»¡¢dbms_random.normal
Õâ¸öº¯Êý²»´ø²ÎÊý£¬»á»Øµ½normal distributionµÄÒ»¸önumberÃÅÀ֮࣬ËùÒÔ´óµÖËæ»úÊý»áÔÚ-Ò»µ½Ò»Æä¼ä¡£
¼òÂÔ²âÊÔÁËÒ»ÏÂ×Ó£¬·¢×÷100000´Î×î´óÄܵ½ÎåÉÏÏ£º
SQL> declare
¶þ i number:=Áã;
Èý j number:=Áã;
ËÄ begin
Îå for ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ