日期:2014-05-16  浏览次数:20639 次

oracle group by rollup decode grouping nvl等判断或者小计合计心得

在做oracle 047第12题时,遇到group by 语句,由此遇到group by  rollup,decode,grouping,nvl,nvl2,nullif,RATIO_TO_REPORT等。

 

1. decode  与if...then,case...when...这类流数据语句功能差不多

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下:
IF 条件=值1 THEN
    RETURN(值1)
ELSIF 条件=值2 THEN
    RETURN(值2)
    ......
ELSIF 条件=值n THEN
    RETURN(值n)
ELSE
    RETURN(缺省值)
END IF

 

decode(字段或字段的运算,值1,值2,值3)

这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3
当然值1,值2,值3也可以是表达式

 

----比较大小

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1

---根据固定值进行排序 order by

select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3) ;

 

2. group by rollup ,grouping,grouping_id,group_id,grouping sets 聚合函数,还可参考OVER (PARTITION BY),这个是可返回多行的聚合函数,比较深一点,链接:http://blog.csdn.net/fengeh/article/details/24934437

 

rollup:为每个分组返回一个小计,同时为所有分组返回总计;
例如:group by rollup(A,B)

grouping 接收列作为参数,如果列为空,则grouping返回1,否则返回0,该函数与rollup搭配使用。
例如:SQL> SELECT DECODE(GROUPING(EMP_ID), 1, '所有员工', EMP_ID) EMP_ID, 

 nvl2(emp_id,DECODE(GROUPING(MONTH), 1, '员工年度', MONTH),'所有员工年度') ND,
 SUM(AMOUNT)  FROM ALL_SALES  GROUP BY ROLLUP(EMP_ID, MONTH);

 

GROUPING_ID()函数可以接受一列或多列,返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来,GOURPING位向量计算,功能与grouping差不多,格式为GROUPING_ID(字段A,字段B,字段C...)

如下例所示

grade         id          位向量      GROUPING_ID()返回值

非空                     非空              00             0

非空                     空                 01             1

空                        非空              10             2

空                        空                 11             3

 

GROUP_ID() 唯一标识重复组,功能与grouping差不多,不过此字段是根据是否有重复数据来判断的,重复数据就判为1,不重复则为0.

 

grouping sets 类似于union all,不去重,用于group by后面,例如:
 group by grouping sets((STOCK_ID), (STOCK_ID, TYPE_CD, DISCOUNT)));

3.nvl nvl2 nullif 都是NULL字段的处理方法

NULL指的是空值,或者非法值。
NVL (expr1, expr2)->expr1为NULL,返回expr2;不为NULL,返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3