Oracle中常用SQL语句及函数收集

AS更改结果集列的名称或为派生列分配名称，可以省略，也可以保留
[sql] 
select objectid as oid from xuser_mv;  
select objectid oid from xuser_mv;  
select count(*) total from (select objectid from xuser_mv) tableid;  
select count(*) total from (select objectid from xuser_mv) as tableid; //wrong  

to_char：将其他类型转化为想要的char型，如日期转化，小数转化等
[sql] 
select to_char(createddate,'DD/MM/YYYY') dat from xuser_mv;  
select contenttype,to_char(avg(count),'099.99') from xobjectinfo_mv group by contenttype order by 2 desc;  

decode函数:相当于条件判断的if...else if...else...语句，就是当expression如果取值为searchi，则函数返回结果为resulti，否则就是default值。对于这个函数，我们可以输入不同的expression值然后选择不同的结果。比如获取最近30天或者获取之前某一个月的所有数据，可以传递expression值来控制。

DECODE( expression , search1 , result1 [, searchi , resulti]... [, default] )

[sql] 
select * from xuser_mv where   
decode('MONTH','30',createddate,sysdate+30) between sysdate-30 and sysdate or                --if MONTH = 30 then it is true  
decode('MONTH','30',to_char(sysdate+31,'MM/YYYY'),to_char(createddate,'MM/YYYY')) = 'MONTH'; --if MONTH != 30 then it is true and check the month  
coalesce函数：返回第一个非null的值
[sql] 
select coalesce(null*3,0,0.1) dat from xuser_mv;  --value is 0  

round函数,
[sql] 
select contenttype, round(avg(count),2) from xobjectinfo_mv group by contenttype order by 2 desc;  
select round(to_date('22/10/2013','DD/MM/YYYY'),'Q') from xuser_mv;  

trunc，保留小数点后多少位，功能类似于round。只不过round会四舍五入。
[sql] 
select contenttype, trunc(avg(count),2) from xobjectinfo_mv group by contenttype order by 2 desc;  
select trunc(to_date('22/11/2013','DD/MM/YYYY'),'Q') from xuser_mv;  

case语句：个人觉得case语句和decode函数有类似功能
[sql] 
select   
case createddate   
when sysdate-6 then sysdate  --when_expression  
else createddate+6   
end newdate  
from xuser_mv;  
select   
case   
when createddate > sysdate-7 then sysdate  --bool_expression  
else createddate   
end newdate  
from xuser_mv;