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

时间:2021-12-11 20:44:20

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