深入探究SQL row_number()函数

小蓝 • 2024-11-21 01:15 • 编程

一、row_number()函数的概述

SQL row_number()函数是一种用于给每一行分配唯一数字（行号）的窗口函数。它可以将查询结果按照指定的列排序，并基于排序结果分配行号。row_number()函数返回的行号始终是正整数。语法如下：

ROW_NUMBER() OVER (ORDER BY column ASC/DESC)[,partition_column]

其中，ORDER BY关键字用于指定基于哪个列排序，ASC表示升序排序，DESC表示降序排序。partition_column是可选的，用于指定每个分区的依据列，如果未指定，则整个结果集都作为一个分区。

二、语法实例

下面的示例将使用在一个简单的员工表中，此表包含员工姓名、部门、薪资等字段。

CREATE TABLE employees (
  employee_id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees (employee_id, name, department, salary)
VALUES
  (101, 'Alice', 'Sales', 5000),
  (102, 'Bob', 'Operations', 6000),
  (103, 'Charlie', 'Sales', 4500),
  (104, 'David', 'Marketing', 8000),
  (105, 'Emma', 'Operations', 7000);

现在，我们要获取员工表中每个部门的最高薪资，并按照薪资降序排列。可以使用以下SQL语句：

SELECT department, MAX(salary) as Max_Salary
FROM employees
GROUP BY department
ORDER BY Max_Salary DESC;

执行上述语句后，我们得到了每个部门的最高薪资。然而，我们只想知道每个部门的最高薪资排名，该怎么办呢？

这时，我们需要使用row_number()函数：

SELECT department, MAX(salary) as Max_Salary, 
       ROW_NUMBER() OVER (ORDER BY MAX(salary) DESC) as rank
FROM employees
GROUP BY department
ORDER BY Max_Salary DESC;

执行上述SQL语句后，我们得到了每个部门的最高薪资和相应的排名。

三、row_number()函数的常见用途

1. 分组中使用row_number()

对于上述示例中的问题，除了使用row_number()函数外，还可以使用子查询等方法来实现。但是，当我们需要获取排名和分组筛选结果时就需要用到row_number()函数。

例如，要获取每个部门最高薪资排名前两名的员工记录，可以使用以下SQL代码：

SELECT department, name, salary, ROW_NUMBER() OVER 
   (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
WHERE (department, salary) IN 
   (SELECT department, MAX(salary) FROM employees GROUP BY department)
AND rank < 3
ORDER BY department, rank;

上述代码中，ROW_NUMBER()函数的PARTITION BY关键字用于指定分组规则，ORDER BY关键字指定了排序规则。

2. 分页查询中使用row_number()

row_number()函数常用于分页查询中，可以通过设置不同的排名条件，实现不同的分页需求。

例如，要获取员工表第2页的数据，每页显示3条记录，可以使用以下SQL代码实现：

WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY employee_id) as row_num
    FROM employees
)
SELECT employee_id, name, department, salary
FROM CTE
WHERE row_num > 3 AND row_num <= 6
ORDER BY employee_id;

上述代码中，CTE（公共表表达式）用于生成一个包含每个员工的行号的结果集。接下来，我们对该结果集进行过滤，获取第2页的员工数据。

3. 为结果集添加自定义行号

row_number()函数不仅可以为结果集添加基于列排序的行号，还可以为结果集添加自定义行号。

例如，我们需要为分组查询结果添加自定义行号（按照最高薪资降序排列），可以使用以下SQL代码：

SELECT RANK() OVER (ORDER BY Max_Salary DESC) as rank, 
       department, MAX(salary) as Max_Salary
FROM employees
GROUP BY department
ORDER BY Max_Salary DESC;

上述代码中，使用RANK()函数代替row_number()函数，其余代码与之前示例基本相同。

四、总结

SQL row_number()函数是一种强大的窗口函数，能够为结果集添加行号，常用于分组、分页查询等场景。通过本文的阐述，我们可以更加深入地了解row_number()函数的使用方法和应用场景，为我们在日常的数据库开发工作中提供了有力的支持。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/160716.html