SQLÓï¾äPART10

oracle tips
ExistµÄÓ÷¨£º
select gw.ndocid from 
(select ndocid from wf_doc_gw_shouwen union select ndocid from wf_doc_gw_fawen) gw
where
not exists (select null from wf_doc_gw_sn sn where sn.ndocid=gw.ndocid)
2¡£°ÑGW±íºÍSN±íÀïÏàͬµÄNDOCIDÏÔʾ³öÀ´
select gw.ndocid from 
(select ndocid from wf_doc_gw_shouwen union select ndocid from wf_doc_gw_fawen) gw
where
exists (select null from wf_doc_gw_sn sn where sn.ndocid=gw.ndocid)
  
DECODEµÄÓï·¨£ºDECODE(value,if1,then1,if2,then2,if3,then3,...,else)£¬
e.g.:
select decode(max(documentid), null, 1, (max(documentid)+1))) from document;  
results:
if null, display 1
else    display max(documentid)+1
1. export database
cmd
%oracle_home%\bin\exp carmot/carmot@132.159.178.236_igrp2 file=d:\a.dmp
2. oracle enterprise console
connect as sysdba
°²È«ÐÔ£¬ÓÒ¼ü£¬½¨Á¢Óû§carmot_hunan_1,ÃÜÂëÓÃcarmot
¸ødbaȨÏÞ¡£
3. import database
cmd
%oracle_home%\bin\imp carmot_hunan_1/carmot@132.159.178.236_igrp2 file=d:\a.dmp fromuser=carmot
** remarks:
carmot_hunan_1/carmot@132.159.178.236_igrp2
Óû§Ãû/ÃÜÂë@ÅäÖÃÔÚTOADÖеÄÊý¾Ý¿âÃû³Æ
ÀýÈç: TOADÖÐÊý¾Ý¿âµÄÃû³ÆÊÇ:  IGRP2_132.159.178.236, ÔòΪ: carmot_hunan_1/carmot@igrp2_132.159.178.236
Example for complicated select:
 
select distinct first_value(ndocid) over (partition by ndocid order by lv desc) as ndocid,
--first_value(curtitle) over (partition by ndocid order by lv desc) as
curtitle,
first_value(realname) over (partition by ndocid order by lv desc) as realname
from(
select ndocid,curtitle, sys_connect_by_path(realname,' ') realname, level lv from
(select ndocid, curtitle,realname, lag(realname,1,null) over (partition by ndocid order by realname) realname_1
from (select g.ndocid,g.curtitle, u.realname
from wf_doc_gw g, tbuser u
where instr(','||g.cprocuserlist||',',','||u.userid||',')>0)) connect by prior realname=realname_1) order by ndocid
e.g.:
select g.ndocid, g.cproc