Oracle函数解析

 
日期函数

  add_months

增加或减去月份
eg:select to_char(add_months(to_date('2009-3','yyyy-mm'),2),'yyyy-mm') from dual;

日期型数据直接加减day；

eg1:select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

 

LAST_DAY 返回日期的最后一天

eg2:select last_day(sysdate) from dual;

 

 

MONTHS_BETWEEN(date2,date1) 给出date2-date1的月份

eg1:select months_between('19-12月-2008','19-3月-2009') mon_between from dual;

 

eg2:select months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;

 

TRUNC 截取日期

select trunc(sysdate,'month') from dual;

 

ROUND 四舍五入

select round(to_date('17-3月-2009'),'month') from dual;

 

extract() 找出日期或间隔值的字段值 

select extract(month from sysdate) "This Month" from dual; 

select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;

 

next_day(d,day) 日期d之后的第一周中,指定的那天(指定星期的第几天)是什么日期 

select next_day(sysdate,1) value from dual;

 

转换函数：

隐式转换-------add_months('13-3月-2009',2)即为add_months(to_date('13-3月-2009'),2)

显示转换-------建议使用显示的数据类型转换，确保SQL的可靠性和可读性

 

to_date (char,'format_model')   eg:  to_date('2009-3-13','yyyy-mm-dd')     or   to_date('2009-3月-13','yyyy-mon-dd')

 SELECT TO_DATE(2454336,'J') from dual;  注意 'J'则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。

 

to_char (date,'format_model')    eg:    to_char(sysdate,'yyyy-mm-dd day hh24:mi:ss am')     

sql server: cast(int as varchar(20))     convert(varchar(20),number)

select  to_char(sal,'L999,999.999') from scott.emp;前去0

select  to_char(sal,'L000,000.000') from scott.emp;不去0

 

to_number (char,'format_model')   eg:  to_number('$1,600,00','$99,999,99')

 

 

分组函数：

avg、min、max、sum、count

count(*)--------返回组中总记录数目

count(exp)------返回exp值非空的记录数目

count(distinct exp)--------返回exp不重复的非空的记录数

distinct ename,dept

 

通用函数：

1、nvl(exp1,exp2)说明：如果表达式exp1的值为null，则返回exp2的值，否则返回exp1的值。

eg: select empno,sal+nvl(comm,0) from emp;

      select empno,nvl(job,'no job yet') from emp;

 

2、nvl2(exp1,exp2,exp3)说明：如果exp1的值不为null，则返回exp2的值，否则返回exp3的值。

eg:select empno,sal,comm,nvl2(comm,sal+comm,sal) total from emp;

 

3、nullif(exp1,exp2)说明：如果exp1与exp2的值相等，则返回null，否则返回exp1的值

 

4、coalesce (exp1,exp2)说明：依次考察各参数表达式，遇到非null值即停止，并返回该值。

eg:select empno,ename,sal,comm,coalesce(sal+comm,sal,0) from emp;

 

 

group by字句

说明：

1、出现在select列表中的字段，如果不是包含在分组函数中，那么该字段必须同时在group by子句中出现

2、包含在group by子句中的字段必须出现在select列表中

3、可使用where子句限定查询条件

4、可使用order by子句指定排序方式  asc /desc

5、可使用having子句对分组的结果再次过滤

eg: select ename,deptno,sum(sal) from emp where sal>1600 group by deptno,ename having deptno=20 order by sum(sal) desc;

 

分析函数： 报表统计

ROW_NUMBER 返回连续的排位，不论值是否相等  

row_number() over()用法

RANK 具有相等值的行排位相同，序数随后跳跃   rank() over()

DENSE_RANK 具有相等值的行排位相同，序号是连续的 dense_rank() over()  

eg1:  select ename,job deptno,sal,row_number() over(order by sal desc) as sal_rank from scott.emp;

 

eg2:select deptno,ename,sal,comm,rank() over(partition by deptno order by sal desc,comm) rank from emp;

 

eg3:

SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC)

  AS DENRANK   FROM emp e, dept d WHERE e.deptno = d.deptno;