SQL SERVER row_number(), rank(), dense_rank()的区别和用法

• RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。

• DENSE_RANK并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。

• ROW_NUMBER连续排名，即使相同的值，依旧按照连续数字进行排名。

---

语法：

RANK( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

DENSE_RANK( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

ROW_NUMBER( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

用法：

SELECT
DENSE_RANK() OVER(ORDER BY 字段 DESC) FROM 表名;

SELECT
DENSE_RANK() OVER(PARTITION BY 字段1 ORDER BY 字段2 DESC)
FROM 表名;

---

一、DENSE_RANK为例：

1、求第N高的薪水：

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    RETURN (
            SELECT DISTINCT b.salary FROM 
            ( SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS a  FROM Employee ) AS b 
            WHERE a = @N
    );
END

2、求部门工资前三高的所有员工：

WITH cte AS (SELECT *, dense_rank() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS rk
FROM Employee)

SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM cte e JOIN Department d
ON e.departmentId = d.id
WHERE rk < 4

---

二、ROW_NUMBER为例：

1、求连续出现的数字：

SELECT DISTINCT(num) AS ConsecutiveNums
FROM 
(
	SELECT id,num, 
	(ROW_NUMBER() OVER(ORDER BY id) - ROW_NUMBER() OVER(PARTITION BY num ORDER BY id)) AS ranksub 
	FROM Logs
) AS sub 
GROUP BY num,ranksub HAVING count(*)>=3

---

---

总结来说：
rank()排序会有重复的，会出现不连续的排序。
dense_rank()排序相同时会重复，但是各个排序序号都是连续的。
row_number()排序不会重复，且序号连续。