西安网站建设设计的好公司排名,包装设计作品,手机网站设计欣赏,北滘建网站参考的博文出处#xff1a;http://www.cnblogs.com/luhe/p/4155612.html#xff0c;对博文进行了修改新增#xff0c;修改了错误的地方 之前用过row_number()#xff0c;rank()等排序与over( partition by ... ORDER BY ...)#xff0c;这两个比较好理解: 先分组#xff…参考的博文出处http://www.cnblogs.com/luhe/p/4155612.html对博文进行了修改新增修改了错误的地方 之前用过row_number()rank()等排序与over( partition by ... ORDER BY ...)这两个比较好理解: 先分组然后在组内排名。 今天突然碰到sum(...) over( partition by ... ORDER BY ... )居然搞不清除怎么执行的所以查了些资料做了下实操。 1. 从最简单的开始 sum(...) over( )对所有行求和 sum(...) over( order by ... )和 第一行 到 与当前行同序号行的最后一行的所有值求和文字不太好理解请看下图的算法解析。 with aa as
(
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 8 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,
sum(c) over(order by b) sum1,--有排序求和当前行所在顺序号的C列所有值
sum(c) over() sum2--无排序求和 C列所有值 from aa 补充 by 松门一枝花 WITH aa AS( SELECT 1 a,1 b, 3 c FROM dualUNIONSELECT 2 a,2 b, 3 c FROM dualUNIONSELECT 3 a,3 b, 3 c FROM dualUNIONSELECT 4 a,4 b, 3 c FROM dualUNIONSELECT 5 a,5 b, 3 c FROM dualUNIONSELECT 6 a,5 b, 3 c FROM dualUNIONSELECT 7 a,2 b, 3 c FROM dualUNIONSELECT 8 a,2 b, 8 c FROM dualUNIONSELECT 9 a,3 b, 3 c FROM dual)
SELECT a,b,c,SUM(c) over(order by a) sum1,--有排序求和当前行所在顺序号的C列所有值--【博主新增的】SUM(c) over(order by b) sum2,--有排序求和当前行所在顺序号的C列所有值SUM(c) over() sum3 FROM aa order by a; --无排序求和 C列所有值2. 与 partition by 结合 sum(...) over( partition by... )同组内所行求和 sum(...) over( partition by... order by ... )同第1点中的排序求和原理只是范围限制在组内 with aa as
(
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 7 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sumFROM aa;
