欢迎来到天天文库
浏览记录
ID:33192058
大小:122.50 KB
页数:13页
时间:2019-02-21
《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
此文档下载收益归作者所有