sql语句一些实用技巧for oracle

kg8khw23tb

　　1）在select语句中使用条件逻辑

select ename,sal,   
case when sal <= 2000 then 'UNDERPAID'  
when sal >= 4000 then 'OVERPAID'  
else 'OK'  
end as status   
from emp  
　　
　　2）从表中随机返回n条记录

select *   
from (   
select ename, job   
from emp   
order by dbms_random.value()   
)   
where rownum <= 5
　　
3）按照子串排序

--比如要从EMP表中返回员工名字和职位，并且按照职位字段最后2个字符排序
select ename,job   
from emp   
order by substr(job,length(job)-2)
　　
4）处理空值排序


--被排序的列存在空值，如果希望空值不影响现有排序
select ename,sal,comm   
from emp   
order by comm nulls last
　　
5）根据数据项的键排序


--比如如果job是“SALESMAN”，根据COMM排序，否则根据SAL排序
select ename,sal,job,comm   
from emp   
order by case when job = 'SALESMAN' then comm else sal end  
　　
6）从一个表中查找另一个表中没有的值

--比如要从DEPT中查找在表EMP中不存在数据的所有部门（数据中，DEPTNO值为40的记录在表EMP中不存在）
select deptno from dept   
minus   
select deptno from emp
　　
7）在运算和比较时使用null值

--null不会等于和不等于任何值，null和自己都不等于。以下例子是当comm有null的情况下列出比“WARD”提成低的员工。 （coalesce函数将null转换为其他值）
select ename,comm,coalesce(comm,0)   
from emp   
where coalesce(comm,0) < ( select comm   
from emp   
where ename = 'WARD' )
　　
8）删除重复记录


--对于名字重复的记录，保留一个
delete from dupes   
where id not in ( select min(id)   
from dupes   
group by name )
　　
9）合并记录


--如果表EMP_COMMISSION中的某员工也存在于EMP表，那么更新comm为1000
--如果以上员工已经更新到1000的员工，如果他们SAL少于2000，删除他们
--否则，从表中提取该员工插入表EMP_COMMISSION

merge into emp_commission ec   
using (select * from emp) emp   
on (ec.empno=emp.empno)   
when matched then  
update set ec.comm = 1000   
delete where (sal < 2000)   
when not matched then  
insert (ec.empno,ec.ename,ec.deptno,ec.comm)   
values (emp.empno,emp.ename,emp.deptno,emp.comm)  
　　
10）用sql生成sql


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

　　11）计算字符在字符串里的出现次数

--判断字符串里有多少个‘ , ’
--先计算原字符串长度，再减去去掉逗号的长度，这个差再除以‘，’的长度
select (length('10,CLARK,MANAGER')-   
length(replace('10,CLARK,MANAGER',',','')))/length(',')   
as cnt   
from t1  
　　
12）将数字和字母分离

　　原数据是：


DATA

---------------

SMITH800

ALLEN1600

WARD1250

JONES2975

MARTIN1250

BLAKE2850

CLARK2450

SCOTT3000

KING5000

TURNER1500

ADAMS1100

JAMES950

FORD3000

MILLER1300


select replace(   
translate(data,'0123456789','0000000000'),'0') ename,   
to_number(   
replace(   
translate(lower(data),   
'abcdefghijklmnopqrstuvwxyz',   
rpad('z',26,'z')),'z')) sal   
from (   
select ename||sal data from emp   
)   
　　

　　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.