要解决的问题:我们想对查询出来的数据进行排名统计,但又不想在源数据中插入相应的字段。
解决方法:[color=red]以oracle中scott用户下的emp表为例演示[/color]
对所有员工的工资进行统计排名,查询语句如下:
select empno,sal,[color=red]rank() over(order by sal desc)[/color] as sal_rank from emp;
结果如下图:
[img]http://dl2.iteye.com/upload/attachment/0098/9140/5278d8e2-cefb-3997-b26b-d7ab609dcdf5.jpg[/img]
select empno,sal,[color=red]dense_rank() over(order by sal desc)[/color] as sal_rank from emp;
结果如下图:
[img]http://dl2.iteye.com/upload/attachment/0098/9142/7fb209ce-fe7b-3e68-a46f-003b4ff0fe05.jpg[/img]
以上两种情况演示了不同排名算法时对结果的操作,接下来考虑这么一种情况:对指定部门员工的薪水进行排名统计。
解决方法如下:
select empno,deptno,sal,[color=red]rank() over(partition by deptno order by sal desc)[/color] as sal_rank from emp;
结果如下图:
[img]http://dl2.iteye.com/upload/attachment/0098/9147/06f28121-980c-30e4-9440-f2ea996763ad.jpg[/img]
[color=darkblue]rank:处理等值排名时占位显示;
dense_rank:处理等值排名时不占位;
partition by column_name:指定在某范围内进行排名;
over(order by column_name):指定以哪个字段进行排名统计。[/color]
解决方法:[color=red]以oracle中scott用户下的emp表为例演示[/color]
对所有员工的工资进行统计排名,查询语句如下:
select empno,sal,[color=red]rank() over(order by sal desc)[/color] as sal_rank from emp;
结果如下图:
[img]http://dl2.iteye.com/upload/attachment/0098/9140/5278d8e2-cefb-3997-b26b-d7ab609dcdf5.jpg[/img]
select empno,sal,[color=red]dense_rank() over(order by sal desc)[/color] as sal_rank from emp;
结果如下图:
[img]http://dl2.iteye.com/upload/attachment/0098/9142/7fb209ce-fe7b-3e68-a46f-003b4ff0fe05.jpg[/img]
以上两种情况演示了不同排名算法时对结果的操作,接下来考虑这么一种情况:对指定部门员工的薪水进行排名统计。
解决方法如下:
select empno,deptno,sal,[color=red]rank() over(partition by deptno order by sal desc)[/color] as sal_rank from emp;
结果如下图:
[img]http://dl2.iteye.com/upload/attachment/0098/9147/06f28121-980c-30e4-9440-f2ea996763ad.jpg[/img]
[color=darkblue]rank:处理等值排名时占位显示;
dense_rank:处理等值排名时不占位;
partition by column_name:指定在某范围内进行排名;
over(order by column_name):指定以哪个字段进行排名统计。[/color]