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
　　----------