sql remark

chomp
----------
primary key: unique and NOT NULL identifier to each record, special case of unique keys. So diff. with unique index:
(1) One is key, one is index;
(2) primary key NOT null.
----------
over: support function like count()rank()... func() over(), func() over(order by xxx), func() over(partition by xxx order by xxx)
order by: sequence to rank by
partition by: base on partition to build senquence, e.g. part_A:1,1,2,3...  part_B:1,2,2...
---------- rollup / cube 分组统计
GRADE     ID       NUM
------  ------   -------
  a       1         1
  a       2         2
  b       3         4
  b       4         4
select grade, ID, decode(grouping_id(grade,ID),1,'小计',3,'合计',ID) ID1, sum(num) from a group by rollup(grade,ID);
GRADE     ID      ID1     NUM
------  ------  ------  -------
  a       1       1        1
  a       2       2        2
  a      null    小计      3
  b       3       3        4
  b       4       4        4
  b      null    小计      8
 null    null    合计     11
rollup: {Col_1 subtotal}, {total}

select grade, ID, sum(num) from a group by cube(grade,ID);
GRADE     ID      NUM
------  ------  -------
  a       1        1
  a       2        2
  a      null      3
  b       3        4
  b       4        4
  b      null      8
 null     1        1
 null     2        2
 null     3        4
 null     4        4
 null    null     11
cube: {Col_1 subtotal}, {Col_2 subtotal}, {total}
-- http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm#i1007428

----------

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值