Oracle²ã´Î²éѯºÍ·ÖÎöº¯ÊýÔںŶÎÑ¡È¡ÖеÄÓ¦ÓÃ
Oracle²ã´Î²éѯºÍ·ÖÎöº¯ÊýÔںŶÎÑ¡È¡ÖеÄÓ¦ÓÃ
ÕªÒª
Ò»×éÁ¬ÐøµÄÊý£¬È¥µôÖмäһЩÊý£¬ÈçºÎÇó³öʣϵÄÊýµÄÇø¼ä£¨¼´ºÅ¶Î£©£¿ÖªµÀºÅ¶ÎµÄÆðÖ¹£¬ÈçºÎÇó³ö¸ÃºÅ¶ÎÄÚËùÓеÄÊý£¿ÖªµÀÒ»¸ö´óµÄºÅ¶Î·¶Î§ºÍÒѾȡ¹ýµÄºÅ¶Î£¬ÈçºÎÇó³ö¿ÉÓõĺŶΣ¿ÀûÓÃOracleÌṩµÄÇ¿´óµÄ²éѯ¹¦ÄÜÒÔ¼°·ÖÎöº¯Êý£¬ÎÒÃÇ¿ÉÒÔºÜÇáËɵĽâ¾öÉÏÊöÎÊÌâ¡£
¹Ø¼ü´Ê£º
ºÅ¶ÎÑ¡È¡¡¢Á¬ÐøÊý¡¢¶Ïµã¡¢²ã´Î²éѯ¡¢·ÖÎöº¯Êý¡¢connect by¡¢rownum¡¢level¡¢lead¡¢lag
1. ÎÊÌâµÄÌá³ö
ÔÚʵ¼Ê¹¤×÷ÖУ¬ÎÒÃdz£³£»áÅöµ½ºÅ¶ÎÑ¡È¡µÄÎÊÌ⣬ÀýÈ磺
Ò»×éÁ¬ÐøµÄÊý£¬È¥µôÖмäһЩÊý£¬ÒªÇó³öʣϵÄÊýµÄÇø¼ä£¨¼´ºÅ¶Î£©
ÀýÈ磺һ´®Êý×ÖΪ1,2,3,4,7,9,10£¬ÔòºÅ¶ÎΪ1-4,7-7,9-10
ÖªµÀºÅ¶ÎµÄÆðÖ¹£¬ÒªÇó³ö¸ÃºÅ¶ÎÄÚËùÓеÄÊý
ÀýÈ磺ºÅ¶ÎΪ1-3,15-15£¬ÔòºÅ¶ÎÄÚËùÓеÄÊýΪ1,2,3,15
Ò»×éÊý£¬Öмä¿ÉÄÜÓжϵ㣬ҪÇó³öȱʧµÄÊý
ÀýÈ磺һ´®Êý×ÖΪ1,2,3,4,7,9,10£¬ÔòȱʧµÄÊýΪ5,6,8
ÒÑÖª´óºÅ¶Î·¶Î§¼°ÒÑÓúŶη¶Î§£¬Çó¿ÉÓúŶη¶Î§
ÀýÈ磺´óºÅ¶Î·¶Î§0-999,ÒÑÓúŶη¶Î§0-200,399-599£¬Ôò¿ÉÓúŶη¶Î§Îª201-398,600-999
2. »ù´¡ÖªÊ¶
ÏÈ×öÏÂÈÈÉíÔ˶¯£¬»Ø¹Ëһϲã´Î²éѯºÍlead£¯lagº¯ÊýµÄÔËÓá£
2.1 αÁÐrownumºÍlevel
αÁоÍÊDz¢·ÇÔÚ±íÖÐÕæÕý´æÔÚµÄÁС£ÒÑÓкܶà×ÊÁϽéÉÜrownumºÍlevelÕâÁ½¸öαÁС£ÕâÀïÖ»ÏëÇ¿µ÷Ò»µã£¬Î±ÁÐÊÇÖ»Õë¶Ô½á¹û¼¯µÄ¡£
2.2 ÀûÓòã´Î²éѯ¹¹ÔìÁ¬ÐøµÄÊý
²úÉú5¡«8Õâ4¸öÁ¬ÐøµÄÊý
PHP code:
select * from (select rownum+4 from dual connect by rownum<5);
select * from (select level+4 from dual connect by level<5);
........
ÒÔ8ÔÂΪ½ç£¬ÀýÈç2005Äê8ÔÂ1ÈÕ£¬Ö®Ç°µÄÔÚУѧÉúÈëѧÄê·ÝΪ2001¡«2004£¬Ö®ºóµÄΪ2002¡«2005¡£Çóµ±Ç°ÈÕÆÚϵÄÔÚУѧÉúÈëѧÄê·Ý£º
PHP code:
select * from (select to_char(add_months(sysdate, 4), 'yyyy') - rownum from dual connect by rownum<
Ïà¹ØÎĵµ£º
select nvl2(replace(translate('69584.00.00','.0123456789','000000000000'),'0',''),'·ñ','ÊÇ') IsNumber from dual;
select id,nvl2(replace(translate(id,'.0123456789','000000000000'),'0',''),'·ñ','ÊÇ') IsNumber
from tbl2 ......
ºÃûÓÐÅöSolarisÁË£¬ÊÖÓÐЩÉúÁË£¬¿´À´ÕæҪװ¸ösolaris£¬Æ½Ê±¶àÍæÍæ¡£
ÔÚsolarisÉÏ×°Oracle 10gÏÈ¡£
1. ¼ì²é°²×°ÒÀÀµ°ü
¹Ù·½ÒªÇóµÄ°ü
SUNWarc
SUNWbtool
SUNWhea
SUNWlibm
SUNWlibms
SUNWsprot
SUNWtoo
SUNWi1of
SUNWi1cs
SUNWi15cs
SUNWxwfnt
Ö´ÐÐÃüÁî
# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SU ......
--²é³öËùÓб»ËøסµÄ±í
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY, c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v$locked_object a,dba_objects b, v$session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;
--ͨ¹ýSID, SERIAL½âËø
--alter system kill sessio ......
ÎÒ¶ÔORACLE BI µÄETLµÄһЩ×ܽᣨԣ© ÊÕ²Ø
http://blog.chinaunix.net/u/25176/showart_2036107.html
Êý¾Ý²Ö¿âÖеÄETLÏêϸµÄ·ÖΪËĸö½×¶Î£ºÌáÈ¡£¬´«Ê䣬ת»»£¬×°ÔØ¡£ÎÒÏȼòµ¥µÄ½éÉÜÒ»ÏÂÌáÈ¡ºÍ´«ÊäµÄ·ÖÀàºÍ·½·¨£º
Ò»£ºÌáÈ¡
ÌáÈ¡¿ÉÒÔ·ÖΪÂß¼ÌáÈ¡£¬ºÍÎïÀíÌáÈ¡¡£
1£ºÂß¼ÌáÈ¡°´ÕÕ¹æÄ£·ÖΪ£ºÍ ......
±íÖÐÒª¸üеÄÊý¾Ý£¬±ØÐëҪͨ¹ýºÍÁíÒ»¸ö±íÀ´¹ØÁª²ÅÄÜÈ·¶¨Òª¸üеÄÌõÄ¿£¬mssql¿ÉÒÔÖ±½ÓÓà from t1.id=t2.id À´¸üУ¬¶øOracleÔò±¨´í
×îºógoogleµ½ÕâÑù£¬Ö±½Ó½«¹ØÁªºóµÄ±í×÷Ϊһ¸ö±íÀ´¸üС£¡£¡£¡£
update (select * from us_cm_cat_petrol_gun g
left join us_cm_ci c on g.machine_sn ......