作者:安静2502917547 | 来源:互联网 | 2014-07-11 17:33
sql语句一些实用技巧fororacle1)在select语句中使用条件逻辑[sql]selectename,sal,casewhensal<2000then'UNDERPAID'whensal>4000then'OVER...
sql语句一些实用技巧for oracle
1)在select语句中使用条件逻辑
[sql]
select ename,sal,
case when sal <= 2000 then &#39;UNDERPAID&#39;
when sal >= 4000 then &#39;OVERPAID&#39;
else &#39;OK&#39;
end as status
from emp
www.2cto.com
2)从表中随机返回n条记录
[sql]
select *
from (
select ename, job
from emp
order by dbms_random.value()
)
where rownum <= 5
3)按照子串排序
[sql]
--比如要从EMP表中返回员工名字和职位,并且按照职位字段最后2个字符排序
select ename,job
from emp
order by substr(job,length(job)-2)
www.2cto.com
4)处理空值排序
[sql]
--被排序的列存在空值,如果希望空值不影响现有排序
select ename,sal,comm
from emp
order by comm nulls last
5)根据数据项的键排序
[sql]
--比如如果job是“SALESMAN”,根据COMM排序,否则根据SAL排序
select ename,sal,job,comm
from emp
order by case when job = &#39;SALESMAN&#39; then comm else sal end
6)从一个表中查找另一个表中没有的值
[sql]
--比如要从DEPT中查找在表EMP中不存在数据的所有部门(数据中,DEPTNO值为40的记录在表EMP中不存在)
select deptno from dept
minus
select deptno from emp
www.2cto.com
7)在运算和比较时使用null值
[sql]
--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 = &#39;WARD&#39; )
8)删除重复记录
[sql]
--对于名字重复的记录,保留一个
delete from dupes
where id not in ( select min(id)
from dupes
group by name )
9)合并记录
[sql]
--如果表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)
www.2cto.com
10)用sql生成sql
[sql]
select &#39;select count(*) from &#39;||table_name||&#39;;&#39; cnts
from user_tables;
11)计算字符在字符串里的出现次数
[sql]
--判断字符串里有多少个‘ , ’
--先计算原字符串长度,再减去去掉逗号的长度,这个差再除以‘,’的长度
select (length(&#39;10,CLARK,MANAGER&#39;)-
length(replace(&#39;10,CLARK,MANAGER&#39;,&#39;,&#39;,&#39;&#39;)))/length(&#39;,&#39;)
as cnt
from t1
12)将数字和字母分离
原数据是:
www.2cto.com
DATA
---------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300
[sql]
select replace(
translate(data,&#39;0123456789&#39;,&#39;0000000000&#39;),&#39;0&#39;) ename,
to_number(
replace(
translate(lower(data),
&#39;abcdefghijklmnopqrstuvwxyz&#39;,
rpad(&#39;z&#39;,26,&#39;z&#39;)),&#39;z&#39;)) sal
from (
select ename||sal data from emp
)
www.2cto.com
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.