1:rank() over(partition by order by)实现分组 排序
oracle实现格式1:select* (select code 编号,salary 工资,rank()over(order by salary desc) 排名 from CT_OA工资 )他rownum<5
eg:查询结果以salary 降序排列取前五名,有排名一样的也取前五个
select id ,salary ,rank() over(partition by class order by score)rk from dual order by rk
oracle实现格式2:select code 编号,salary 工资,rank()over(partition by class order by salary desc) 排名 from CT_OA工资
eg:查询结果先以class 分组 ,在以salary 降序排列,
总结:partition by进行分组,rank+order by 进行给每个分组内的记录进行排序。
rank() over实现占位排序
与row_number区别
row_number()和rank()有个区别:row_number()不会出现相同排序,一直递增;就算两条记录参与排序的字段的数值一样,排序也是递增
https://blog.csdn.net/m0_37960071/article/details/80545992
2:mysql 实现占位排序
实现目的: 按照班级分类后按照分数倒序排序
采用mysql变量简单实现,SQL如下:
SELECT a.stu_id,a.point,
IF( @className = a.class_name,@rank := @rank + 1,@rank :=1 ) AS rank,
(@className := a.class_name) class_name
FROM TMP_A a, ( select @rank :=0,@className := NULL ) b
ORDER BY a.class_name, a.point DESC ;
排序
SELECT a.stu_id,a.point,
@rank:=case when @className = a.class_name then @rank := @rank + 1
when @className := a.class_name then @rank :=1
when a.class_name =0 then @rank :=1 else ‘’ end rank,
@className := a.class_name
FROM TMP_A a, ( select @rank :=0,@className := NULL ) b
ORDER BY a.class_name, a.point DESC ;
class_name 如果partition by有值,字段为partition by后的字段,
如果order by有值,字段为order by后的字段,已此字段累计出现次数排序后取前五条,占位