sql語句一些實用技巧for oracle

1）在select語句中使用條件邏輯

select ename,sal,   
       case when sal <= 2000 then 'UNDERPAID'  
            when sal >= 4000 then 'OVERPAID'  
            else 'OK'  
       end as status   
from emp  

2）從表中隨機返回n條記錄

select *   
  from (   
    select ename, job   
    from emp   
    order by dbms_random.value()   
  )   
where rownum <= 5 

3）按照子串排序

--比如要從EMP表中返回員工名字和職位，並且按照職位字段最後2個字符排序

select ename,job   
  from emp   
order by substr(job,length(job)-2) 

4）處理空值排序

--被排序的列存在空值，如果希望空值不影響現有排序 

select ename,sal,comm   
   from emp   
order by comm nulls last 

5）根據數據項的鍵排序

--比如如果job是“SALESMAN”，根據COMM排序，否則根據SAL排序 

select ename,sal,job,comm   
  from emp   
order by case when job = 'SALESMAN' then comm else sal end  

6）從一個表中查找另一個表中沒有的值

--比如要從DEPT中查找在表EMP中不存在數據的所有部門（數據中，DEPTNO值為40的記錄在表EMP中不存在） 

select deptno from dept   
minus   
select deptno from emp 

7）在運算和比較時使用null值

--null不會等於和不等於任何值，null和自己都不等於。以下例子是當comm有null的情況下列出比“WARD”提成低的員工。 （coalesce函數將null轉換為其他值）

select ename,comm,coalesce(comm,0)
from emp
where coalesce(comm,0) < ( select comm
from emp
where ename = 'WARD' )

8）刪除重復記錄
 

--對於名字重復的記錄，保留一個

delete from dupes   
 where id not in ( select min(id)   
                      from dupes   
                     group by name ) 

9）合並記錄
 

--如果表EMP_COMMISSION中的某員工也存在於EMP表，那麼更新comm為1000 
--如果以上員工已經更新到1000的員工，如果他們SAL少於2000，刪除他們 
--否則，從表中提取該員工插入表EMP_COMMISSION 


merge into emp_commission ec   
using (select * from emp) emp   
    on (ec.empno=emp.empno)   
 when matched then  
       update set ec.comm = 1000   
       delete where (sal < 2000)   
 when not matched then  
       insert (ec.empno,ec.ename,ec.deptno,ec.comm)   
       values (emp.empno,emp.ename,emp.deptno,emp.comm)  

10）用sql生成sql
 

select 'select count(*) from '||table_name||';' cnts   
  from user_tables;   

11）計算字符在字符串裡的出現次數

--判斷字符串裡有多少個‘ , ’
--先計算原字符串長度，再減去去掉逗號的長度，這個差再除以‘，’的長度
select (length('10,CLARK,MANAGER')-   
  length(replace('10,CLARK,MANAGER',',','')))/length(',')   
  as cnt   
from t1  

12）將數字和字母分離

原數據是：

DATA
---------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300
 

 select replace(   
   translate(data,'0123456789','0000000000'),'0') ename,   
   to_number(   
     replace(   
     translate(lower(data),   
       'abcdefghijklmnopqrstuvwxyz',   
       rpad('z',26,'z')),'z')) sal   
   from (   
     select ename||sal data from emp   
 )   

ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300

思路是很復雜的，比如先去除數字，是先把所有數字翻譯為0，然後用replace去掉0.