SQL语句一些实用技巧for oracle.docVIP

SQL语句一些实用技巧for oracle  1）在select语句中使用条件逻辑 1select 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 UNDERPAIDALLEN 1600 UNDERPAIDWARD 1250 UNDERPAIDJONES 2975 OKMARTIN 1250 UNDERPAIDBLAKE 2850 OKCLARK 2450 OKSCOTT 3000 OKKING 5000 OVERPAIDTURNER 1500 UNDERPAIDADAMS 1100 UNDERPAIDJAMES 950 UNDERPAIDFORD 3000 OKMILLER 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 PRESIDENTSMITH CLERKADAMS CLERKJAMES CLERKMILLER CLERKJONES MANAGERCLARK MANAGERBLAKE MANAGERALLEN SALESMANMARTIN SALESMANWARD SALESMANTURNER SALESMANSCOTT ANALYSTFORD ANALYST 4）处理空值排序 当被排序的列存在空值，如果希望空值不影响现有排序 1select ename,sal,comm 2 from emp 3order by comm nulls last ENAME SAL COMM------ ----- ---------TURNER 1500 0ALLEN 1600 300WARD 1250 500MARTIN 1250 1400SMITH 800JONES 2975JAMES 950MILLER 1300FORD 3000ADAMS 1100BLAKE 2850CLARK 2450SCOTT 3000KING 5000 1select ename,sal,comm 2 from emp 3order by comm desc nulls first ENAME SAL COMM------ ----- ----------SMITH 800JONES 2975CLARK 2450BLAKE 2850SCOTT 3000KING 5000JAMES 950MILLER 1300FORD 3000ADAMS 1100MARTIN 1250 1400WARD 1250 500ALLEN 1600 300TURNER 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 0ALLEN 1600 SALESMAN 300WARD 1250 SALESMAN 500SMITH 800 CLERKJAMES 950 CLERKADAMS 1100 CLERKMARTIN 1250 SALESMAN 1300MILLER 130