grouping and grouping_id

--grouping值为0时说明这个值是数据库中本来的值，为1说明是统计的结果(也可以说该列为空时是1，不为空时是0)，
with t as
 (select 1 id, 'A' class_id, 10 amt
    from dual
  union all
  select 2 id, 'A' class_id, 20 amt
    from dual
  union all
  select 3 id, 'A' class_id, 30 amt
    from dual
  union all
  select 4 id, 'A' class_id, 40 amt
    from dual
  union all
  select 5 id, 'B' class_id, 10 amt
    from dual
  union all
  select 6 id, 'B' class_id, 20 amt
    from dual
  union all
  select 7 id, 'B' class_id, 30 amt
    from dual
  union all
  select 8 id, 'B' class_id, 40 amt
    from dual)
SELECT CLASS_ID,
       DECODE(GROUPING(CLASS_ID), '0', 'sub-total', 'total'),
       SUM(AMT)
  FROM T
 GROUP BY ROLLUP(CLASS_ID);

/*
grouping_id(id,grade)和grouping_id(grade,id)的值是不同的，因为GROUPING_ID()函数可以接受一列或多列，
返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来，所以说01和10的值不一样的

GOURPING位向量计算

如下例所示

grade         id          位向量      GROUPING_ID()返回值

非空           非空         00             0

非空           空           01             1

空            非空          10             2

空            空            11             3
*/

with t as
 (select 1 id, 'A' class_id, 10 amt
    from dual
  union all
  select 2 id, 'A' class_id, 20 amt
    from dual
  union all
  select 3 id, 'A' class_id, 30 amt
    from dual
  union all
  select 4 id, 'A' class_id, 40 amt
    from dual
  union all
  select 5 id, 'B' class_id, 10 amt
    from dual
  union all
  select 6 id, 'B' class_id, 20 amt
    from dual
  union all
  select 7 id, 'B' class_id, 30 amt
    from dual
  union all
  select 8 id, 'B' class_id, 40 amt
    from dual)
SELECT id,
       CLASS_ID,
       DECODE(GROUPING_id(CLASS_ID, id), '1', 'sub-total','0',null, 'total'),
       SUM(AMT)
  FROM T
 GROUP BY ROLLUP(CLASS_ID, id);