高级排序函数:
[ ROW_NUMBER()| RANK() | DENSE_RANK ] OVER (partition by xx order by xx)
1.row_number() 连续且递增的数字 1 2 3 4
row_number() over (partition by xx order by xx )
--学生表中按照所在专业分组,同专业内按成绩倒序排序,成绩相同则按学号正序排序,并给予组内等级
select row_number() over(partition by class_id order by score desc)rn,t.* from student2016 t
2.rank() 跳跃排序 若有相同数据则排名相同 然后跳跃排序 1 2 2 2 5
rank() over (partition by xx order by xx )
=======作者注释===============
ank() over (order by 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)
=======作者注释===============
select rank() over(partition by class_id order by score desc)rn,t.* from student2016 t
3.dense_rank 若有相同数据则排名相同 然后递增排序
dense_rank over (partition by xx order by xx ) 1 2 2 2 3
select dense_rank() over(partition by class_id order by score desc)rn,t.* from student2016 t
----------------------------------------------------------------------------------------------------------------------------
高级分组函数
group by rollup(a,b,c)
select a,b,c,sum(d) from test group by rollup(a,b,c)
对rollup后面的列 按从右到左以少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)
对于n个参数的 rollup,有n+1次分组
即按a,b,c,分组,union all a,b分组 union all a分组 union from test
----------------------------------------------------------------------------------
group by cube(a,b,c)
对n个参数,有2^n次分组
即按 ab,ac,a,bc,b,c最后对 全部分组
----------------------------------------------------------------------------------
group by grouping sets(a,b)
即只列出 对 a分组后,和对 b分组的结果集
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
|