[每日一题] OCP1z0-047 :2013-07-22 group by子句(三)
-------- ---------- ---------- ---------- ---------- ----------
29110311 1 10 57.5 465 0.46268656
29110311 2 10 70 420 0.41791044
29110311 3 10 40 120 0.11940298
小计 56.25 1005 1
29110312 1 20 60 120 0.17910447
29110312 2 15 60 300 0.44776119
29110312 3 50 50 250 0.37313432
小计 56.6666666 670 1
29110313 1 15 70 210 0.35593220
29110313 3 10 80 160 0.27118644
29110313 3 20 55 220 0.37288135
小计 68.3333333 590 1
12 rows selected
QUOTE:
--------------------------------------------------------------------------------
原帖由 dingjun123 于 2011-2-23 10:13 发表
理解分组的概念
sum(sum(........之后相当于什么样的分组，后面又来个sum(.............
当然报错了
--------------------------------------------------------------------------------
还是不理解 ，
暂不看sum,这里的 分子。分母是一样的啊。只是sum了分母，还是sum了分子；
相当于 sum(sum(a))/sum(a) 与 sum(a)/sum(sum(a)) ,后面的行的通，前面的怎么可能行不通啊
理解这个
with t as
(select mod(level,2) id from dual connect by level<10 )
select id,sum(id) from t
group by id;
with t as
(select mod(level,2) id from dual connect by level<10 )
select-- id,
sum(sum(id)) from t
group by id;
第2句为什么不能有id,因为sum(sum,外面的sum相当于全量分组了，相当于
with t as
(select mod(level,2) id from dual connect by level<10 )
select sum(x) from (
select id,
sum(id) x from t
group by id
);
那么当然不能有非汇总列在select里显示啊
一针见血啊。高~，很耐心的指导啊
还有个疑问
select
sum(sum(QTY )) over (partition by stock_Id) PCT1 ,
sum(sum(QTY )) over (partition by stock_Id,grouping(type_cd)) PCT2
from t_dist
group by rollup(stock_id,(type_cd,DISCOUNT))
PCT1,PCT2有何区别？我实在是想不懂了。
等价于这个
select sum(x) over (partition by stock_Id) PCT1,
sum(x) over(partition by stock_Id,gp) PCT2
from (
select
sum(QTY ) x,stock_Id,grouping(type_cd) gp
from t_dist
group by rollup(stock_id,(type_cd,DISCOUNT))
);
看红色部分就知道第3个加了grouping的值，那么分区（分组）是不同的，所以两个结果不同