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
----------
页:
[1]