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

6）從一個(gè)表中查找另一個(gè)表中沒有的值

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

1select deptno from dept   
2minus   
3select deptno from emp 

7）在運(yùn)算和比較時(shí)使用null值

null不會(huì)等于和不等于任何值，null和自己都不等于。以下例子是當(dāng)comm有null的情況下列出比“WARD”提成低的員工。 （coalesce函數(shù)將null轉(zhuǎn)換為其他值）

1select ename,comm,coalesce(comm,0)   
2  from emp   
3where coalesce(comm,0) < ( select comm   
4                                     from emp   
5                                    where ename = 'WARD' )

ENAME COMM COALESCE(COMM,0)
---------- ---------- ----------------
SMITH 0
ALLEN 300 300
JONES 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0 0
ADAMS 0
JAMES 0
FORD 0
MILLER 0

8）刪除重復(fù)記錄

對(duì)于名字重復(fù)的記錄，保留一個(gè)

1delete from dupes   
2 where id not in ( select min(id)   
3                      from dupes   
4                     group by name ) 

9）合并記錄
比如如下需求：
如果表EMP_COMMISSION中的某員工也存在于EMP表，那么更新comm為1000
如果以上員工已經(jīng)更新到1000的員工，如果他們SAL少于2000，刪除他們
否則，從表中提取該員工插入表EMP_COMMISSION

1merge into emp_commission ec   
2using (select * from emp) emp   
3    on (ec.empno=emp.empno)   
4 when matched then  
5       update set ec.comm = 1000   
6       delete where (sal < 2000)   
7 when not matched then  
8        insert (ec.empno,ec.ename,ec.deptno,ec.comm)   
9       values (emp.empno,emp.ename,emp.deptno,emp.comm)  

10）用sql生成sql

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

（user_tables是oracle的元數(shù)據(jù)表之一）

CNTS
----------------------------------------
select count(*) from ANT;
select count(*) from BONUS;
select count(*) from DEMO1;
select count(*) from DEMO2;
select count(*) from DEPT;
select count(*) from DUMMY;
select count(*) from EMP;
select count(*) from EMP_SALES;
select count(*) from EMP_SCORE;
select count(*) from PROFESSOR;
select count(*) from T;
select count(*) from T1;
select count(*) from T2;
select count(*) from T3;
select count(*) from TEACH;
select count(*) from TEST;
select count(*) from TRX_LOG;
select count(*) from X;

11）計(jì)算字符在字符串里的出現(xiàn)次數(shù)

1select (length('10,CLARK,MANAGER')-   
2  length(replace('10,CLARK,MANAGER',',','')))/length(',')   
3  as cnt   
4from t1   

先計(jì)算原字符串長度，再減去去掉逗號(hào)的長度，這個(gè)差再除以‘，’的長度

12）將數(shù)字和字母分離

 1select replace(   
 2  translate(data,'0123456789','0000000000'),'0') ename,   
 3  to_number(   
 4    replace(   
 5    translate(lower(data),   
 6      'abcdefghijklmnopqrstuvwxyz',   
 7      rpad('z',26,'z')),'z')) sal   
 8  from (   
 9    select ename||sal data from emp   
10 )   

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

思路是很復(fù)雜的，比如先去除數(shù)字，是先把所有數(shù)字翻譯為0，然后用replace去掉0.

13）根據(jù)表中的行創(chuàng)建分割列表

 1select deptno,   
 2    ltrim(sys_connect_by_path(ename,','),',') emps   
 3  from (   
 4  select deptno,   
 5    ename,   
 6    row_number() over   
 7       (partition by deptno order by empno) rn,   
 8    count(*) over   
 9      (partition by deptno) cnt   
10  from emp   
11  )   
12  where level = cnt   
13  start with rn = 1   
14    connect by prior deptno = deptno and prior rn = rn-1 

查詢結(jié)果
DEPTNO EMPS
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

14）按字母順序排序

 1select old_name, new_name   
 2  from (select old_name, replace(sys_connect_by_path(c, ' '), ' ') new_name   
 3          from (select e.ename old_name,   
 4                       row_number() over(partition by e.ename order by substr(e.ename, iter.pos, 1)) rn,   
 5                       substr(e.ename, iter.pos, 1) c   
 6                  from emp e, (select rownum pos from emp) iter   
 7                 where iter.pos <= length(e.ename)   
 8                 order by 1) x   
 9         start with rn = 1   
10        connect by prior rn = rn - 1   
11               and prior old_name = old_name)   
12 where length(old_name) = length(new_name) 

You would like the result to be:

OLD_NAME NEW_NAME
---------- --------
ADAMS AADMS
ALLEN AELLN
BLAKE ABEKL
CLARK ACKLR
FORD DFOR
JAMES AEJMS
JONES EJNOS
KING GIKN
MARTIN AIMNRT
MILLER EILLMR
SCOTT COSTT
SMITH HIMST
TURNER ENRRTU
WARD ADRW