Oracle的SQL函数

1.     日期函数

 

add_months(date_value,number)从第一个日期值，加减去的月份,正数加,负数减

last_day(date_value) 可以返回月份的最后一天的日期

months_between(date_value1, date_value2)返回两个日期之间的月份数量

Round  (d1[,format_mask])以中午12点的四舍五入

   select round(sysdate,'year') from dual 返回2007年的1月1日,精确到年份;

Next_day  (d1,n)返回未来的第一个星期的日期，n为1 表示星期天，7表示星期六

Trunc (d1) 取靠近d1日期的整数（00：00：00）

―Greatest  (d1,d2,d3…) 取参数中最大的一个

 

2.     字符函数

 

Substr (str,n1,n2) 取从str的第n1 位的起的n2 个字符

Instr (str,S,n1,n2)返回Str的S这个字符从第n1位出现n2次的所在位数

Lpad(str,n,’-’)返回长度为n的str，位数不够在左补’-’

Rpad(str,n,’-’) 返回长度为n的str，位数不够在右补’-’

Trim() ltrim() rtrim()  去空格的

Decode() 判断赋值函数

      SELECT Decode (col_name, 1, 'Southlake', 2, 'San Francisco',  'Non-domestic')

      --col_name列的数值为1，则显示Southlake，为2则显示San Francisco，

 

 

3．数字函数

 

Round（base_number,int）四舍五入到小数点右的第int位，负数为小数字左边第int位

Trunc(base_number,int)舍去到小数点右的第int位，负数为小数字左边第int位

Sign（number）大于0的number返回1，小于返回-1,为0返回0

Ln(number)返回number的自然对数

4．常规函数

nvl(qty,0)

    Select  nvl(price,'no data') from dept –-price 列为NULL的数显示no data

nvl2(qty,1,0)

    --qty列为NULL，则显示1，不为NULL,则显示1

NULLIF（s,s1）  两个值相同，返回空，否则，返回第一个参数

COALESCE(N个参数) 返回第一个非空值的参数

 

5.分组函数

Stddev

variance

 

 

 

7.Oracle的Cube,Rollup子句的用法

 产生交叉报表的情况，演示一个cube的例子

　　create table test(sales varchar2(10),dest varchar2(10),revenue number);
　　insert into test values('smith','hangzhou',1000);
　　insert into test values('smith','wenzhou',2000);
　　insert into test values('allen','wenzhou',3000);
　　insert into test values('allen','wenzhou',4000);

    SALES　　　DEST　　　　　REVENUE
　　----------     ----------                   ----------
　　smith　　　hangzhou　　　　 1000
　　smith　　　wenzhou　　　　　2000
　　allen　　　 wenzhou　　　　　3000
　　allen　　　 wenzhou　　　　　4000
　　
　　比如说我们想统计每个sales的总销售收入，每个sales在各个城市的销售收入，另外还想知道每个城市所有sales的销售收入总额，以及所有sales的总收入
　　就像以下报表
　　名字　　 hangzhou    wenzhou         总计

       -------      ------------  -----------      ---------
　　allen　　　　　　　　7000　　　 7000
　　smith　　1000　　　　2000　　　 3000
　　　　　　1000　　　　9000　　　 10000
　　　　
　　那我们运行下面这条语句就行了
　  select sales,dest,sum(revenue) from test group by cube(sales,dest);
　　SALES　　　DEST　　SUM(REVENUE)
　　----------     ----------      ------------
　　　　　　　　　　　　 10000　     所有sales的总销售收入
　　　　　  　 wenzhou　  9000　　   所有sales在温州的销售收入
　　　　　　    hangzhou　 1000　　   所有sales在杭州的销售收入
　　allen　　　　　　　　　7000　     allen的所有销售收入
　　allen　　　  wenzhou　  7000　      allen在温州的销售收入
　　smith　　　　　　　　 3000　      smith所有的销售收入
　　smith　　 　wenzhou　  2000　      smith在温州的销售收入
　　smith　　 　hangzhou　 1000         　 smith在杭州的销售收入
　　8 rows selected.
　　如果不想统计城市这个维度，那么用rollup子句
　　
　　　　　　　 hangzhou wenzhou
　　allen　　　　7000　　7000
　　smith　　　1000　　 2000　　　3000
　　　　　　　　　　　　　　　　　　　　　10000
　　
      select sales,dest,sum(revenue) from test group by rollup(sales,dest)
　　
　　SALES　　　DEST　　　 SUM(REVENUE)
　　----------      ----------            ------------------
　　allen　　　 wenzhou　　　 7000　         allen在温州的销售收入
　　allen　　　　　　　　　   7000　         allen的所有销售收入
　　smith　　　wenzhou　　 　2000                     smith在温州的销售收入
　　smith　　　hangzhou　　   1000                      smith在杭州的销售收入
　　smith　　　　　　　　　  3000　          smith 所有的销售收入
　　　　　　　　　　　　　　10000               　所有sales的总销售收入
　　

        select * from test;