透過例項講解 SQL RANK() 函式

摘要：在本教程中，您將學習如何使用 SQL RANK() 函式來查詢結果集中每一行的排名。

SQL RANK() 函式簡介 #

RANK() 函式是一個視窗函式，它為結果集分割槽中的每一行分配一個排名。

一行的排名由其前面有多少個排名加一決定。

RANK() 函式的語法如下：

RANK() OVER (
	PARTITION BY <expr1>[{,<expr2>...}]
	ORDER BY <expr1> [ASC|DESC], [{,<expr2>...}]
)Code language: SQL (Structured Query Language) (sql)

在此語法中：

首先，PARTITION BY 子句根據一個或多個條件將結果集中的行分配到不同的分割槽。
其次，ORDER BY 子句對每個分割槽中的行進行排序。
第三，RANK() 函式在每個分割槽的行上操作，並在跨越每個分割槽邊界時重新初始化。

相同列值的行會獲得相同的排名。當多行共享同一個排名時，下一行的排名將不是連續的。這類似於奧運會獎牌的授予方式，即如果有兩名運動員並列獲得金牌，那麼就不會有銀牌。

以下語句建立了一個名為 t 的新表，並插入了一些示例資料：

CREATE TABLE t (
	col CHAR
);
	
INSERT INTO t(col)
VALUES('A'),('B'),('B'),('C'),('D'),('D'),('E');
	
	
SELECT 
	*
FROM
	t;
Code language: SQL (Structured Query Language) (sql)

以下語句使用 RANK() 函式為結果集的行分配排名：

SELECT
	col,
	RANK() OVER (
		ORDER BY col
	) myrank
FROM
	t;
Code language: SQL (Structured Query Language) (sql)

下圖顯示了輸出結果：

如輸出中清楚顯示的，第二行和第三行共享相同的排名，因為它們的值相同。第四行獲得的排名是 4，因為 RANK() 函式跳過了排名 3。

請注意，如果您希望排名是連續的，可以使用 DENSE_RANK() 函式。

SQL RANK() 函式示例 #

我們將使用示例資料庫中的 employees 和 departments 表進行演示。

在結果集上使用 SQL RANK() 函式的示例 #

以下語句按員工的薪水進行排名：

SELECT 
	first_name, 
	last_name, 
	salary, 
	RANK() OVER (ORDER BY salary) salary_rank
FROM 
	employees;
Code language: SQL (Structured Query Language) (sql)

下面顯示了部分輸出：

SQL Rank Function over result set example

在這個例子中，我們省略了 PARTITION BY 子句，因此整個結果集被視為一個單獨的分割槽。

ORDER BY 子句按薪水對結果集中的行進行了排序。然後，RANK() 函式根據員工薪水的降序順序應用於結果集中的每一行。

在分割槽上使用 SQL RANK() 函式的示例 #

以下語句查詢其所在部門中薪水第二高的員工：

WITH payroll AS (
	SELECT 
		first_name, 
		last_name, 
		department_id,
		salary, 
		RANK() OVER (
			PARTITION BY department_id
			ORDER BY salary) salary_rank
	FROM 
		employees
)
SELECT 
	first_name, 
	last_name,
	department_name,
	salary
FROM 
	payroll p
	INNER JOIN departments d 
		ON d.department_id = p.department_id
WHERE 
	salary_rank = 2;	
Code language: SQL (Structured Query Language) (sql)

在公用表表達式（CTE）中，我們按部門查詢員工的薪水排名：

首先，PARTITION BY 子句按部門將員工記錄劃分為不同的分割槽。
然後，ORDER BY 子句按薪水對每個分割槽中的員工進行排序。
最後，RANK() 函式為每個分割槽內的員工分配排名。薪水相同的員工獲得相同的排名。

下圖展示了公用表表達式的部分結果集：

外部查詢僅選擇了薪水排名為 2 的員工。它還與 departments 表進行了連線，以便在最終結果集中返回部門名稱。

下圖顯示了查詢的輸出：

SQL Rank Function over partition example

在本教程中，您學習瞭如何使用 SQL RANK() 函式為結果集中的每一行分配排名。

資料庫 #

本教程是否有幫助？