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

oracle olapº¯Êý

/*sum()over()*/
--ĬÈϼÆËãËùÓÐÐеĺϼÆ
select t.empno,t.ename,t.sal,t.deptno,sum(t.sal)over()
from scott.emp t;
--partition by·Ö×éºÏ¼Æ
select t.empno,t.ename,t.sal,t.deptno,
       sum(t.sal)over(partition by t.deptno)
from scott.emp t
order by t.deptno,t.sal;
--partition by order by deptno·Ö×éÀÛ¼Æ
select t.empno,t.ename,t.sal,t.deptno,
       sum(t.sal)over(partition by t.deptno order by t.sal)
from scott.emp t;
--rows n preceding È¡µ±Ç°ÐÐ+ǰnÐÐ=(n+1)ÐÐ
--ͨ¹ýorder by desc¿ÉÒÔÈ¡ºónÐÐ
select t.empno,t.ename,t.sal,t.deptno,
       sum(t.sal)over(order by t.deptno,t.sal rows 1 preceding)
from scott.emp t;
--rows 2n+1 È¡µ±Ç°ÐÐ+ǰnÐÐ+ºónÐÐ=(2n+1)ÐÐ
select t.empno,t.ename,t.sal,t.deptno,
       sum(t.sal)over(order by t.deptno,t.sal rows between 1 preceding and 1 following)
from scott.emp t;
/*first_value() over()*/
select deptno,ename,sal,hiredate,
¡¡¡¡first_value(ename) over(partition by deptno order by sal asc rows 5 preceding) first_ename
¡¡¡¡from emp  order by hiredate asc;
/*avg()over  count() over()  max()over() min()over()*/
select deptno,sal,
    sum(sal)over(partition by deptno) as sumsal,
¡¡¡¡avg(sal)over(partition by deptno) as avgsal,
¡¡¡¡count(*)over(partition by deptno) as count,
¡¡¡¡max(sal)over(partition by deptno) as maxsal
from emp;
/*rank()over() dese_rank()over() row_number()over()*/
select empno, deptno, sal,
    rank()   over (order by deptno desc nulls last) as rank,
    dense_rank()  over (partition by deptno order by sal desc nulls last) as dense_rank,
    row_number()  over(partition by deptno order by sal desc nulls last) as row_number
from emp;
/*stddev() over()*±ê×¼²î/
select empno, deptno, sal,stddev(sal) over(order by sal)
from emp;


Ïà¹ØÎĵµ£º

ORACLE ÁÙʱ±í¿Õ¼äʹÓÃÂʹý¸ßµÄÔ­Òò¼°½â¾ö·½°¸

ORACLE ÁÙʱ±í¿Õ¼äʹÓÃÂʹý¸ßµÄÔ­Òò¼°½â¾ö·½°¸(2009-11-14 19:59:02)
±êÇ©£ºoracle ÁÙʱ±í¿Õ¼ä ʹÓÃÂÊ100 ½â¾ö·½°¸ it
·ÖÀࣺ¼¼Êõ²©ÂÛ
ÔÚÊý¾Ý¿âµÄÈÕ³£Ñ§Ï°ÖУ¬·¢ÏÖ¹«Ë¾Éú²úÊý¾Ý¿âµÄĬÈÏÁÙʱ±í¿Õ¼ätempʹÓÃÇé¿ö´ïµ½ÁË30G£¬Ê¹ÓÃÂÊ´ïµ½ÁË100%£» ´ýµ÷ÕûΪ32Gºó£¬Ê¹ÓÃÂÊ»¹ÊÇΪ100%£¬µ¼Ö´ÅÅ̿ռäʹÓýôÕÅ¡£¸ù¾ÝÁÙʱ±í¿Õ¼äµÄÖ÷ ......

oracle sqlplus ³£ÓÃÃüÁî´óÈ«

showºÍsetÃüÁîÊÇÁ½ÌõÓÃÓÚά»¤SQL*Plusϵͳ±äÁ¿µÄÃüÁî
SQL> show all --²é¿´ËùÓÐ68¸öϵͳ±äÁ¿Öµ
SQL> show user --ÏÔʾµ±Ç°Á¬½ÓÓû§
SQL> show error¡¡¡¡ --ÏÔʾ´íÎó
SQL> set heading off --½ûÖ¹Êä³öÁбêÌ⣬ĬÈÏֵΪON
SQL> set feedback off --½ûÖ¹ÏÔʾ×îºóÒ»ÐеļÆÊý·´À¡ÐÅÏ¢£¬Ä¬ÈÏֵΪ"¶Ô6¸ö» ......

³õѧORACLEµÄÀ§»ó

     ¶Ô³õѧORACLEµÄÈËһʱºÜÄÑ·ÖÇåORACLEÖйØÓÚ“¿Õ¼ä”µÄ¸ÅÄ±ÈÈç±í¿Õ¼ä ÁÙʱ±í¿Õ¼ä Óû§±í¿Õ¼äµÈ£¬¶ÔÓÚÓÉSQLתµ½ORACLEµÄ¾Í¸ü¼Ó»ìÏýÁË£¬ÈÃÈ˸оõORACLE·±Ëö£»·±ËöÔÚÄĶù£¬ÎÒÕûÀíÂÞÁÐÈçÏ£º
1.ORACLE×Ô´ø¹¤¾ß²»Ê®·ÖÒ×Óã»
2.ORACLEµÚÈý·½¹¤¾ßÒ×ÉÏÊÖ£¬µ«ÄÑÉîÈ룻
   &n ......

OracleͬÒå´Ê£¬¸øÄã´øÀ´µÄ¼¸¸ö¾ªÏ²

OracleÊý¾Ý¿âÖÐÌṩÁËͬÒå´Ê¹ÜÀíµÄ¹¦ÄÜ¡£Í¬Òå´ÊÊÇÊý¾Ý¿â·½°¸¶ÔÏóµÄÒ»¸ö±ðÃû£¬¾­³£ÓÃÓÚ¼ò»¯¶ÔÏó·ÃÎʺÍÌá¸ß¶ÔÏó·ÃÎʵݲȫÐÔ¡£ÔÚʹÓÃͬÒå´Êʱ£¬OracleÊý¾Ý¿â½«Ëü·­Òë³É¶ÔÓ¦·½°¸¶ÔÏóµÄÃû×Ö¡£ÓëÊÓͼÀàËÆ£¬Í¬Òå´Ê²¢²»Õ¼ÓÃʵ¼Ê´æ´¢¿Õ¼ä£¬Ö»ÓÐÔÚÊý¾Ý×ÖµäÖб£´æÁËͬÒå´ÊµÄ¶¨Òå¡£ÔÚOracleÊý¾Ý¿âÖеĴ󲿷ÖÊý¾Ý¿â¶ÔÏó£¬Èç±í¡¢ÊÓͼ¡¢Í ......

oracleÖ®°Ñ±íÎļþµ¼³ö³ÉÎı¾Îļþ

1 spool
spool d:\test\table.dat
select * from table_name;
spool off
2 sqlplus user/password@sid @test.sql > table.dat
test.sql
     select * from table_name;
     exit; ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ