分组求和SQL⽰例
1、ROLLUP和CUBE函数，⾃动汇总数据
lect * from test_tbl的数据这样的
col_a col_b col_c
---- ----- -----
成洪才
1 b1 12
1 b1 2
1 b
2 31
2 b2 7
2 b
3 42
2 b
3 1
2 b
3 3
如果按A、B列进⾏汇总C列，⽤⼀般的⽅法是这样：
lect col_a,col_b,sum(col_c) from test_tbl group by col_a,col_b 结果如下
col_a col_b sum(col_c)
---- ----- --------
1 b1 14
1 b
2 31
2 b2 7
2 b
3 46
但是如果这时候还想按A列汇总且要C列的合计数，那就要再⽤两个SQL来嵌套，很⿇烦，不过⽤rollup就简单多了：
lect nvl(col_a,'合计') col_a,nvl(col_b,decode(col_a,null,'','⼩计'||col_a))
col_b,sum(col_c)
from test_tbl group by rollup(col_a,col_b)，结果如下
col_a col_b sum(col_c)
---- ----- --------
1 b1 14
专业实习报告1 b
2 31
1 ⼩计1 45
2 b2 7
2 b
3 46
2 ⼩计2 53
合计 98
结果集刚好是先按A和B汇总，然后是按A汇总，最后是全部汇总这时候如果再要按B列汇总，怎么办呢？⼜要⽤SQL嵌套吗？不是的，如果有这要求的话，改⽤cube函数就OK啦
lect nvl(col_a,decode(col_b,null,'合计','⼩计'||col_b)) col_a,nvl(col_b,decode
(col_a,null,'','⼩计'||col_a)) col_b,sum(col_c)
from test_tbl group by cube(col_a,col_b) 结果如下
施工进度计划表范本col_a col_b sum(col_c)
---- ----- --------
1 b1 14
1 b
2 31
1 ⼩计1 45
2 b2 7
2 b
3 46
2 ⼩计2 53
⼩计b1 b1 14
⼩计b2 b2 38
⼩计b3 b3 46
合计 98
跟刚才rollup函数得到的结果集有点不⼀样，那就是多了些按B列的汇总⾏。
2、LAG和LEAD函数，⾃动链接上/下⾏记录值
怎么选牛奶
SQL> desc test_tbl
Name Type
----- ------
COL_K NUMBER
现在按顺序的往这个test_tbl表中插⼊⼀系列数据，下⾯是SQL：
inrt into test_tbl values(1)
inrt into test_tbl values(2)
inrt into test_tbl values(4)
inrt into test_tbl values(5)
inrt into test_tbl values(8)
inrt into test_tbl values(9)
inrt into test_tbl values(11)
inrt into test_tbl values(12)
inrt into test_tbl values(13)
........
数据插完后，要检查插⼊的数据中，从最⼩数到最⼤数之间有那些数是没被插⼊表，找出这些数的
前⼀个和后⼀个数？如这个例⾥从1到13当中有⽬字3、6、7、10没被插⼊表中，这些数的前⼀个和后⼀个分别是2和4、5和8、9和11，即
PREV_VAL NEXT_VAL
---------- ----------
2 4
5 8
9 11
如果不⽤分析函数要得到这后结果集那真不敢想象是怎么样的⼀段SQL，但⽤LAG分析函数那就简单了，这样写就OK
lect prev_val,next_val from(
lect col_k next_val,lag(col_k,1,0) over (order by col_k) prev_val from test_tbl
) where next_val-prev_val>1
对于LEAD函数是⼀样的，只不过它是往后链接⽽已。
3、RANK和DENSE_RANK函数，对数据进⾏排名
测试表是这样的lect *from test_tbl结集如下
COL_A COL_B
---------- ----------
脾虚泄泻A 242
A 233
B 154
C 287
C 76
D 66
E 154
F 154
G 212
G 43
按A列来统计B列的值，⽤⼀般的SQL是这样lect col_a, sum(col_b) from test_tbl group by
col_a order by 2 desc 结果是这样
COL_A SUM(COL_B)
---------- ----------
A 475
C 363
G 255
B 154
F 154
E 154
D 66
从这个数据集可以看出A是最⼤的，C是第⼆⼤的，当数据多时就不知道谁是排第⼏了，这时⽤DENSE_RANK可以达到这⽬的
lect col_a,sum(col_b),den_rank() over (order by sum(col_b) desc) ranks from
test_tbl group by col_a 结果如下
COL_A SUM(COL_B) RANKS
---------- ---------- ----------
A 475 1
C 363 2
G 255 3
B 154 4
F 154 4
E 154 4
D 66 5鞋子大了一码补救方法
这个数据集把每个值都排了名次，可以直接看得出，相同值的名次是相同的。
⽤RANK跟DENSE_RANK差不多，不过就是当出现在名次相同时，下⼀个名次会跳跃
lect col_a,sum(col_b),rank() over (order by sum(col_b) desc ) ranks from test_tbl
group by col_a 结果如下
COL_A SUM(COL_B) RANKS
---------- ---------- ----------
A 475 1
C 363 2
G 255 3
B 154 4
F 154 4
E 154 4
D 66 7
可以看到名次从4跳跃到7，就是因为名次4重复出现了两次
实际应⽤中可能会⽐这些例⼦要复杂多点，可能会先对表的数据分组，然后再⽤分析，如
lect *from test_tbl的结果是这样的
COL_G COL_A COL_B
-
--------- ---------- ----------
G1 A 242
G1 A 233
G2 C 287
G2 C 76
G2 D 66
G2 E 154打工记
G3 F 154
G3 G 212
G3 G 43
G2 B 154
对这个数据集按G和A列汇总B列进⾏排名，就要先对表按G列进⾏分组，然后再按A列汇总B列值进⾏排名
lect col_g,col_a,sum(col_b),den_rank() over (partition by col_g order by sum
(col_b) desc ) ranks
from test_tbl
group by col_g,col_a这个SQL加了partition by先按G列分组，结果如下
COL_G COL_A SUM(COL_B) RANKS
---------- ---------- ---------- ----------
G1 A 475 1
G2 C 363 1一键局域网共享
G2 B 154 2
G2 E 154 2
G2 D 66 3
G3 G 255 1
G3 F 154 2
可以看到名次都是在G列的组别发⽣变化时，就会重新开始新排列
;
