oracle查詢操作注意事項

oracle查詢操作注意事項

1、模糊查詢
 如果要將萬用字元%和_作為字元值使用，必須在escape後使用逸出字元，舉例如下：
--尋找g3e_username欄位以‘PT_’開頭的記錄
select * from G3E_DIALOGTAB where g3e_username like 'PTa_%' escape 'a'

2、order by 子句的使用
當在select語句中同時包含有多個子句（where，group by，having，order by）等時，order by必須是最後一條子句。

3、關於多表插入的問題
使用all操作符執行多表插入
insert all
when deptno = 10 then into dept10
when deptno = 20 then into dept20
when deptno = 30 then into dept30
when job = 'CLERK' then into CLERK
else into other
  select * from emp;

使用first多表插入
採用first多表插入，如果資料已經滿足了先前的條件，且已經插入到了某表，那麼改行資料在後續的插入中不會被再次使用。
insert first
when deptno = 10 then into dept10
when deptno = 20 then into dept20
when deptno = 30 then into dept30
when job = 'CLERK' then into CLERK
else into other
  select * from emp;

4、group by和having的使用
使用group by子句進行分組統計時，挑選清單中的列必須出現在group by子句中；而且只能使用having來限制分組顯示結果（不能使用where子句）。

5、在group by子句中使用rollup或cube
select operator,district,sum(cur_conduit_len) from N9_RPT_USERDATA group by rollup(operator,district);--對operator按district再進行一個總計
select operator,district,sum(cur_conduit_len) from N9_RPT_USERDATA group by cube(operator,district);--縱向統計 除了對operator進行總計，還對district進行一個總計

6、grouping函數的使用
 該函數用於確定統計結果是否用到了特定列，用到的則用1表示，反之為0.
select operator,district,sum(cur_conduit_len),grouping(operator),grouping(district) from N9_RPT_USERDATA group by rollup(operator,district);

7、grouping sets操作符的使用
 用於顯示分組統計結果
select operator,district,sum(cur_conduit_len) from N9_RPT_USERDATA group by grouping sets(operator,district);

8、(+)操作符的使用
   1）(+)操作符只能出現在where子句中，且不能與outerjoin文法同時使用。
   2）當使用(+)操作符操作符執行外串連時，如果where子句中包含有多個條件，則必須在所有條件中都包含(+)操作符。
第二點的錯誤讓我印象很深刻，之前寫了個語句，因為在where子句中包含有多個條件，但是沒有都指定(+)操作符，導致結果的錯誤。

select b.district 子領域,
       sum(decode(a.ownership, '自建', 1, 0)) 自建吊線段數量,
       sum(decode(a.ownership, '自建', 0, 1)) 其它吊線段數量,
       sum(decode(a.ownership, '自建', a.length, 0)) 自建吊線段長度,
       sum(decode(a.ownership, '自建', 0, a.length)) 其它吊線段長度
  from b$i_hangline_info a, b$gc_netelem b
 where a.g3e_fid(+) = b.g3e_fid
   and a.feature_state<> '拆除' --這個條件是後來加上的，正是這個條件的添加導致了統計結果的錯誤
   and b.district in ('寶山','長寧','崇明','奉賢','虹口','黃浦','嘉定','金山','靜安','盧灣','普陀','浦東','青浦','松江','徐匯','楊浦','閘北','閔行')
 group by b.district;

以上語句查詢出來的結果沒有全，是因為採用外串連的情況下，a.feature_state<> '拆除'這個條件沒有加外串連操作符，導致在統計中第一列的統計值為0的情況下就不顯示了，儘管該列後面的結果不為0.只要修改如下就可以正常顯示查詢結果了：
select b.district 子領域,
       sum(decode(a.ownership, '自建', 1, 0)) 自建吊線段數量,
       sum(decode(a.ownership, '自建', 0, 1)) 其它吊線段數量,
       sum(decode(a.ownership, '自建', a.length, 0)) 自建吊線段長度,
       sum(decode(a.ownership, '自建', 0, a.length)) 其它吊線段長度
  from b$i_hangline_info a, b$gc_netelem b
 where a.g3e_fid(+) = b.g3e_fid
   and a.feature_state(+) <> '拆除'
   and b.district in ('寶山','長寧','崇明','奉賢','虹口','黃浦','嘉定','金山','靜安','盧灣','普陀','浦東','青浦','松江','徐匯','楊浦','閘北','閔行')
 group by b.district;

3）(+)操作符只能適用於列，而不能用在運算式上。
4）(+)操作符不能與OR和IN操作符一起使用。
5）(+)操作符只能用於實現左外串連或右外串連，而不能用於實現完全外串連。

9、在多行子查詢中使用all或any
單列單行或單列多行子查詢
1）使用all操作符
all操作符必須與單行操作符結合使用，並且返回行必須要匹配於所有子查詢結果。
select * from g3e_attribute where g3e_ano >all (select g3e_ano from g3e_tabattribute where g3e_dtno=247303381);

2)使用any操作符
all操作符必須與單行操作符結合使用，並且返回行只需匹配於子查詢的任一個結果即可。
select * from g3e_attribute where g3e_ano >any (select g3e_ano from g3e_tabattribute where g3e_dtno=247303381);

多列多行子查詢
成對比較
select * from i_exch_info where (gwm_fid,gwm_fno) in (select gwm_fid,gwm_fno from gc_netelem where gwm_fno=25057) ;

非成對比較：
select * from i_exch_info where gwm_fid in (select gwm_fid from gc_netelem where gwm_fno=25057) and gwm_fno in (select gwm_fno from gc_netelem where gwm_fno=25057);

10、在查詢中注意使用minus和intersect
  minus可以替代not in 或not exists；intersect可以替代and