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.cprocuserlist from wf_doc_gw g;
result:
101     1     1001,1002,1003
102     2     101,1004

select u.userid, u.realname from tbuser
1001     a1
1002     a2
1003     a3
1004     a4

select g.ndocid,g.curtitle, u.realname
from wf_doc_gw g, tbuser u
where instr(','||g.cprocuserlist||',',','||u.userid||',')>0)
result:
101     1     a1
101     1     a2
101     1     a3
102     2     a1
102     2     a4

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
result:
101     1     a1 a2 a3
102     2     a1 a4