1.trunc()函数
1)格式化日期
语法:trunc(date[, fmt])
date 一个日期值
fmt 日期格式
用法:
select trunc(sysdate) from dual 结果:2011-3-10
select trunc(sysdate ,'dd') from dual 结果:2011-3-10
select trunc(sysdate ,'mm') from dual 结果:2011-3-1
select trunc(sysdate ,'yyyy') from dual 结果:2011-1-1
与round()函数比较(没有指定日期格式):
round函数的日期如果在当天中午12点之前,结果是当天的时间,否则是第二天的
select round(to_date('2011-03-10 11:20:20', 'yyyy-mm-dd hh24:mi:ss')) from dual 结果:2011-03-10
select round(to_date('2011-03-10 13:20:20', 'yyyy-mm-dd hh24:mi:ss')) from dual 结果:2011-03-11
trunc()函数则不会出现
select trunc(to_date('2011-03-10 11:20:20', 'yyyy-mm-dd hh24:mi:ss')) from dual 结果:2011-03-10
select trunc(to_date('2011-03-10 13:20:20', 'yyyy-mm-dd hh24:mi:ss')) from dual 结果:2011-03-10
2)格式化数值
语法:trunc(number[,decimals])
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数
用法:
trunc(89.985,2) = 89.98
trunc(89.985)= 89
trunc(89.985,-1) = 80
注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。
2.decode()函数
语法:decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
或 decode(value,if1,then1,if2,then2,if3,then3,...,else)
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
例:
1)给公司职员加工资,其标准是:工资在8000元以下的将加20%;工资在8000元以上的加15%
select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary) from employee
2) 比较大小:select decode(sign(变量1-变量2),-1,变量1,变量2) from dual;
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
如:select decode(sign(10-20), -1, 10, 1, 20) from dual; 结果:10
3)表、视图结构转化
现有一个商品销售表sale,表结构为:
month char(6) --月份
sell number(10,2) --月销售金额
现有数据为:
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要转化为以下结构的数据:
year char(4) --年份
month1 number(10,2) --1月销售金额
month2 number(10,2) --2月销售金额
month3 number(10,2) --3月销售金额
month4 number(10,2) --4月销售金额
month5 number(10,2) --5月销售金额
month6 number(10,2) --6月销售金额
month7 number(10,2) --7月销售金额
month8 number(10,2) --8月销售金额
month9 number(10,2) --9月销售金额
month10 number(10,2) --10月销售金额
month11 number(10,2) --11月销售金额
month12 number(10,2) --12月销售金额
结构转化的SQL语句为:
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
这个结构转换,要比写语句方便多了
4)结合Lpad函数,如何使主键的值自动加1并在前面补0
select lpad(decode(count(记录编号),0,1,max(to_number(记录编号)+1)),14,'0') 记录编号 from tetdmis
lpad(String a,int length,String addString). 把addString添加到a的左边,length 是返回值的长度。