如何在SQL Server中为查询结果生成序号？

SQL Server 查询序号

在 SQL Server 中，我们经常需要对查询结果进行排序并加上序号，这可以通过使用ROW_NUMBER()、RANK() 或DENSE_RANK() 等窗口函数来实现，这些函数允许我们在查询结果集中生成行号或排名。

1. 使用ROW_NUMBER() 函数

ROW_NUMBER() 函数为每一行分配一个唯一的行号，从1开始递增，它通常用于需要唯一标识每一行的情况。

语法

SELECT 
    ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY sort_expression) AS RowNum,
    column1, column2, ...
FROM 
    table_name;

示例

假设有一个名为Employees 的表，包含以下列：EmployeeID,FirstName,LastName,Department，我们希望为每个部门的员工按姓氏排序并分配序号。

SELECT 
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY LastName) AS RowNum,
    EmployeeID, FirstName, LastName, Department
FROM 
    Employees;

这个查询将为每个部门的员工按姓氏排序并分配唯一的行号。

2. 使用RANK() 函数

RANK() 函数为每一行分配一个排名，但与ROW_NUMBER() 不同的是，如果两行的值相同，它们将共享相同的排名，并且后续排名会跳过。

语法

SELECT 
    RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression) AS Rank,
    column1, column2, ...
FROM 
    table_name;

示例

假设我们希望根据员工的年薪为员工排名，如果年薪相同则共享排名。

SELECT 
    EmployeeID, FirstName, LastName, Salary,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM 
    Employees;

这个查询将为所有员工按年薪降序排名。

3. 使用DENSE_RANK() 函数

DENSE_RANK() 函数类似于RANK()，但它不会跳过排名，如果两行的值相同，它们将共享相同的排名，并且后续排名是连续的。

语法

SELECT 
    DENSE_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression) AS DenseRank,
    column1, column2, ...
FROM 
    table_name;

示例

假设我们希望根据员工的年薪为员工密集排名，如果年薪相同则共享排名。

SELECT 
    EmployeeID, FirstName, LastName, Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseSalaryRank
FROM 
    Employees;

这个查询将为所有员工按年薪降序密集排名。

相关问题与解答

Q1:ROW_NUMBER()、RANK() 和DENSE_RANK() 之间有什么区别？

A1:

ROW_NUMBER(): 为每一行分配一个唯一的行号，从1开始递增，即使有相同的值，也不会共享行号。

RANK(): 为每一行分配一个排名，相同的值会共享相同的排名，并且后续排名会跳过，如果有两个第一名，那么下一个排名是第三名。

DENSE_RANK(): 类似于RANK()，但不会跳过排名，相同的值会共享相同的排名，并且后续排名是连续的，如果有两个第一名，那么下一个排名是第二名。

Q2: 如何在SQL Server中使用窗口函数对数据进行分区和排序？

A2:

在SQL Server中，可以使用OVER() 子句中的PARTITION BY 和ORDER BY 子句来对数据进行分区和排序。PARTITION BY 用于将数据划分为不同的分区，而ORDER BY 用于在每个分区内进行排序，以下是一个示例：

SELECT 
    EmployeeID, FirstName, LastName, Department, Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptSalaryRank
FROM 
    Employees;

在这个查询中，我们根据Department 列对数据进行分区，然后在每个分区内根据Salary 列进行降序排序，并为每一行分配一个行号。