如何使用SQL Server行号

吴欣
1,052 阅读3分钟

在大多数情况下，当我们需要对数据库中的项目进行编号时，我们会跳到身份属性。然而，当你需要对一个结果的行进行编号时，会发生什么？这就是行号函数发挥作用的地方。

行号函数允许你为SQL查询结果的每一行分配一个连续的数字。

row_number()函数是SQL Server窗口函数的一部分。使用这个函数，你可以为一个结果的每个分区集上的每一行分配一个渐进的整数。每个数字从1开始，并对每个分区中的行进行重置。

函数语法和返回值

该函数的语法如图所示。

ROW_NUMBER ()  
    OVER (partition BY partition_expression
    ORDER BY order_by_expression
);

让我们来分析一下上述语法：

  1. partition by - partition by子句允许你将你的结果集划分为各种逻辑分区。然后将row_number函数应用于每个分区。partition by参数是可选的，如果不指定，row_number函数将把结果集作为一个唯一的分区。
  2. order by子句允许你对每个分区集中的行进行排序。与partition by子句不同，row_number函数需要这个子句作为一个对顺序敏感的函数。

该函数通过给每个分区中的行分配一个连续的数字来返回。如前所述，该函数将为每个新分区重置行号。

SQL Server Row_Number():实例

让我们用一个例子来更好地理解如何使用row_number()函数。首先创建一个带有假数据的样本数据库，如下面的查询中所示。

CREATE DATABASE  dummy_db;
USE dummy_db;
CREATE TABLE dummy_table(
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    firstName VARCHAR(50),
    lastName VARCHAR(50),
    email VARCHAR(100),
    salary money,
    department VARCHAR(50)
);
INSERT INTO dummy_table(firstName, lastName, email, salary, department)
VALUES ('Karen', 'Colmen', 'karen@col.io', $149000, 'Game Development'),
       ('Alex', 'Bell', 'bell.alex@mail.com', $150000, 'Graphics Development'),
       ('Charles', 'Johnson', 'charles@johnson.org', $120500, 'DevOps Development'),
       ('Bruce', 'Greer', 'brucegreer@hotmail.com', $118000, 'Security Development'),
       ('Sarah', 'Austin', 'sarah100@yahoo.com', $165000, 'Game Development'),
       ('Diana', 'Kim', 'kim_diana@protonmail.com', $105000, 'Front-End Development'),
       ('Peter', 'Cogh', 'peter@gmail.com', $100000, 'Graphics Development'),
       ('David', 'Hugh', 'hughdavid@outlook.com', $126000, 'Database Development'),
       ('Tobias', 'Newne', 'tobias@newne.com', $115500, 'Database Development'),
       ('Winnie', 'Lorentz', 'lorentzw@info.de', $175000, 'Graphics Development'),
       ('Guy', 'Miche', 'miche@guy.tv', $145000, 'Game Development');

SELECT * FROM dummy_table;

上述查询应该会返回一个如图所示的结果集。

例子1

下面的SQL语句使用row_number函数为结果集合中的行分配一个连续的数字。

SELECT ROW_NUMBER() OVER (
    ORDER BY salary) AS row_num,
    firstName,
    lastName,
    department
FROM dummy_table;

上面的查询应该返回一个结果集，如下图所示：

例2

我们可以使用row_number函数来定位特定部门中工资最高的员工。

请看下面的查询示例：

SELECT firstName, lastName, salary, department, ROW_NUMBER() OVER (partition BY department ORDER BY salary DESC) AS row_num FROM dummy_table;

上面的查询根据部门将数据划分为逻辑分区。然后我们应用row_number()函数按工资降序排序。

例3

你可以使用row_number函数进行分页。由于row_number函数给所有的行分配了一个连续的数字，我们可以用它来过滤每页的特定数量的结果。

以下面的例子为例：

SELECT * FROM
(SELECT ROW_NUMBER()
        OVER (ORDER BY salary) AS row_num, firstName, lastName, department
    FROM dummy_table) dt
    WHERE row_num >= 1 AND row_num <= 5;

上面的查询应该返回一个输出为：

结论

在本指南中，我们讨论了如何使用SQL Server row_number()函数来给结果集中的行分配顺序号。另外，我们回顾了函数的语法和返回值。我们希望你认为这篇文章对你有帮助。

avatar
文章
阅读
粉丝