SQL Server:排名函数row_number,rank,dense_rank,ntile详解
Posted on 2014-03-29 12:58 且行且思 阅读(725) 评论(0) 编辑 收藏 举报1.Row_Number函数
row_number函数大家比较熟悉一些,因为它的用途非常的广泛,我们经常在分页与排序中用到它,它的功能就是在每一行中生成一个连续的不重复的序号
例如:
select SM,row_number() over(order by SM desc) as ID from salary
注意:over条件中的order by 与 SQL中排序条件order by不冲突。
2.Rank函数
该函数返回每行数据在结果集中的排名,如果有重复的排名,那么下一个排名与上一个排名就会断开,也就是不连续。这有点像我们读书时的成绩排名,实际上,它也在成绩,绩效等排名上用的非常多。
例:
select rank() over(order by SM desc) as id,SM from salary
over条件与Row_Number函数一样。
3.dense_rank函数
我们使用Rank函数得到了不连续的排名,那么如果我们需要连续的排名的时候怎么办呢?呵呵,聪明的你应该已经猜到了,就是使用dense_rank函数,dense_rank函数的用法与Rank函数是一样的,只是它返回每行数据在结果集中连续的排名.
例:
select Dense_rank() over(order by SM desc) as id,SM from salary
4.ntile函数
ntile函数可以将结果集放到我们指定数目的组中。比如我们的测试表salary中有6行数据,现在我们要将这6行数据按照salary值的从大到小的顺序分成4组,那么我们就可以用ntile函数。
示例:
select ntile(4) over(order by SM desc) as id, SM from salary
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
sql server 2005中的分区函数用法(partition by 字段)
partition by关键字是分析性函数的一部分,它和聚合函数不同的地方在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反映统计值的记录,partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组
OVER (PARTITION BY…)的用法
OVER (PARTITION BY…) 按照某列分割/区分
例:
SELECT EMP.*,SUM(SAL) OVER(PARTITION BY DEPTNO) 本部门工资总和 FROM EMP
不需要已某个列的值分割,那就要用null,因为没有分割列,所以“工资总和”的值为所有sal列的值的和
SELECT EMP.*,SUM(SAL) OVER(PARTITION BY DEPTNO) 工资总和 FROM EMP
使用 sum(sal) over (order by ename)... 查询员工的薪水"连续"求和
SELECT EMP.*,SUM(SAL) OVER(ORDER BY ENAME) 按姓名排序后连续求和,100*ROUND(SAL/SUM(SAL) OVER (),4) "份额(%)" FROM EMP
SELECT EMP.*,SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME) 按照部门连续求总和 FROM EMP
SELECT EMP.*,SUM(SAL) OVER (ORDER BY DEPTNO,ENAME) 所有部门连续求总和 FROM EMP
部门从大到小排列,部门里各员工的薪水从高到低排列
SELECT DEPTNO,ENAME,SAL,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO DESC, SAL DESC) DEPT_SUM,
SUM(SAL) OVER(ORDER BY DEPTNO DESC, SAL DESC) SUM FROM EMP