很久以前看某本書整理的，忘了哪本了，現在貢獻出來。不斷補充，總共大概20個左右,抓緊補充中，完成前大家稍等
1）在select語句中使用條件邏輯
1
select ename,sal,
2 case when sal <= 2000 then 'UNDERPAID'
3 when sal >= 4000 then 'OVERPAID'
4 else 'OK'
5 end as status
6from emp
ENAME SAL STATUS
---------- ---------- ---------
SMITH 800 UNDERPAID
ALLEN 1600 UNDERPAID
WARD 1250 UNDERPAID
JONES 2975 OK
MARTIN 1250 UNDERPAID
BLAKE 2850 OK
CLARK 2450 OK
SCOTT 3000 OK
KING 5000 OVERPAID
TURNER 1500 UNDERPAID
ADAMS 1100 UNDERPAID
JAMES 950 UNDERPAID
FORD 3000 OK
MILLER 1300 UNDERPAID
2）從表中隨機返回n條記錄
1select *
2 from (
3 select ename, job
4 from emp
5 order by dbms_random.value()
6 )
7 where rownum <= 5
3）按照子串排序
比如要從EMP表中返回員工名字和職位，并且按照職位字段最后2個字符排序
1select ename,job
2 from emp
3order by substr(job,length(job)-2)
ENAME JOB
---------- ---------
KING PRESIDENT
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
JONES MANAGER
CLARK MANAGER
BLAKE MANAGER
ALLEN SALESMAN
MARTIN SALESMAN
WARD SALESMAN
TURNER SALESMAN
SCOTT ANALYST
FORD ANALYST
4）處理空值排序
當被排序的列存在空值，如果希望空值不影響現有排序
1select ename,sal,comm
2 from emp
3order by comm nulls last
ENAME SAL COMM
------ ----- ---------
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
SMITH 800
JONES 2975
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
1select ename,sal,comm
2 from emp
3order by comm desc nulls first
ENAME SAL COMM
------ ----- ----------
SMITH 800
JONES 2975
CLARK 2450
BLAKE 2850
SCOTT 3000
KING 5000
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
MARTIN 1250 1400
WARD 1250 500
ALLEN 1600 300
TURNER 1500 0
5）根據數據項的鍵排序
比如如果job是“SALESMAN”，根據COMM排序，否則根據SAL排序
1select ename,sal,job,comm
2 from emp
3 order by case when job = 'SALESMAN' then comm else sal end
ENAME SAL JOB COMM
---------- ---------- --------- ----------
TURNER 1500 SALESMAN 0
ALLEN 1600 SALESMAN 300
WARD 1250 SALESMAN 500
SMITH 800 CLERK
JAMES 950 CLERK
ADAMS 1100 CLERK
MARTIN 1250 SALESMAN 1300
MILLER 1300 CLERK
CLARK 2450 MANAGER
BLAKE 2850 MANAGER
JONES 2975 MANAGER
SCOTT 3000 ANALYST
FORD 3000 ANALYST
6）從一個表中查找另一個表中沒有的值
比如要從DEPT中查找在表EMP中不存在數據的所有部門（數據中，DEPTNO值為40的記錄在表EMP中不存在）
1select deptno from dept
2minus
3select deptno from emp
7）在運算和比較時使用null值
null不會等于和不等于任何值，null和自己都不等于。以下例子是當comm有null的情況下列出比“WARD”提成低的員工。 （coalesce函數將null轉換為其他值）
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）刪除重復記錄
對于名字重復的記錄，保留一個
1delete from dupes
2 where id not in ( select min(id)
3 from dupes
4 group by name )
9）合并記錄
比如如下需求：
如果表EMP_COMMISSION中的某員工也存在于EMP表，那么更新comm為1000
如果以上員工已經更新到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的元數據表之一）
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）計算字符在字符串里的出現次數
1select (length('10,CLARK,MANAGER')-
2 length(replace('10,CLARK,MANAGER',',','')))/length(',')
3 as cnt
4from t1
先計算原字符串長度，再減去去掉逗號的長度，這個差再除以‘，’的長度
12）將數字和字母分離
原數據是：
DATA
---------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300
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
思路是很復雜的，比如先去除數字，是先把所有數字翻譯為0，然后用replace去掉0.
13）根據表中的行創建分割列表
表中數據：
DEPTNO EMPS
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD
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
查詢結果
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
posted on 2008-08-07 16:14
henry1451 閱讀(117)
評論(0) 編輯 收藏