sql语句一些实用技巧for oracle

《sql语句一些实用技巧for oracle》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、SQL语句一些实用技巧fororacle1）在select语句中使用条件逻辑1selectename,sal,2casewhensal<=2000then'UNDERPAID'3whensal>=4000then'OVERPAID'4else'OK'5endasstatus6fromempENAMESALSTATUS-----------------------------SMITH800UNDERPAIDALLEN1600UNDERPAIDWARD1250UNDERPAIDJONES2975OKMARTIN1250UNDERPAIDBLAKE2850OKCL

2、ARK2450OKSCOTT3000OKKING5000OVERPAIDTURNER1500UNDERPAIDADAMS1100UNDERPAIDJAMES950UNDERPAIDFORD3000OKMILLER1300UNDERPAID2）从表中随机返回n条记录1select*2from(3selectename,job4fromemp5orderbydbms_random.value()6)7whererownum<=53）按照子串排序比如要从EMP表中返回员工名字和职位，并且按照职位字段最后2个字符排序1selectename,job2fromemp3o

3、rderbysubstr(job,length(job)-2)ENAMEJOB-------------------KINGPRESIDENTSMITHCLERKADAMSCLERKJAMESCLERKMILLERCLERKJONESMANAGERCLARKMANAGERBLAKEMANAGERALLENSALESMANMARTINSALESMANWARDSALESMANTURNERSALESMANSCOTTANALYSTFORDANALYST4）处理空值排序当被排序的列存在空值，如果希望空值不影响现有排序1selectename,sal,comm2frome

4、mp3orderbycommnullslastENAMESALCOMM--------------------TURNER15000ALLEN1600300WARD1250500MARTIN12501400SMITH800JONES2975JAMES950MILLER1300FORD3000ADAMS1100BLAKE2850CLARK2450SCOTT3000KING50001selectename,sal,comm2fromemp3orderbycommdescnullsfirstENAMESALCOMM---------------------SMITH

5、800JONES2975CLARK2450BLAKE2850SCOTT3000KING5000JAMES950MILLER1300FORD3000ADAMS1100MARTIN12501400WARD1250500ALLEN1600300TURNER150005）根据数据项的键排序比如如果job是“SALESMAN”，根据COMM排序，否则根据SAL排序1selectename,sal,job,comm2fromemp3orderbycasewhenjob='SALESMAN'thencommelsesalendENAMESALJOBCOMM---------

6、------------------------------TURNER1500SALESMAN0ALLEN1600SALESMAN300WARD1250SALESMAN500SMITH800CLERKJAMES950CLERKADAMS1100CLERKMARTIN1250SALESMAN1300MILLER1300CLERKCLARK2450MANAGERBLAKE2850MANAGERJONES2975MANAGERSCOTT3000ANALYSTFORD3000ANALYST6）从一个表中查找另一个表中没有的值比如要从DEPT中查找在表EMP中不存在数

7、据的所有部门（数据中，DEPTNO值为40的记录在表EMP中不存在）1selectdeptnofromdept2minus3selectdeptnofromemp7）在运算和比较时使用null值null不会等于和不等于任何值，null和自己都不等于。以下例子是当comm有null的情况下列出比“WARD”提成低的员工。（coalesce函数将null转换为其他值）1selectename,comm,coalesce(comm,0)2fromemp3wherecoalesce(comm,0)<(selectcomm4fromemp5whereename='WAR

8、D')ENAMECOMMCOALESC