SQL Server：排名函数row_number,rank,dense_rank,ntile详解

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