利用ROW_NUMBER() OVER函数给SQL数据库中每一条记录分配行号的方法

利用ROW_NUMBER() OVER函数给SQL数据库中每一条记录分配行号的方法

在SQL数据库中，常常需要对记录进行排序或者其他操作，此时需要给每一条记录分配一个行号来方便处理。这时候就可以使用ROW_NUMBER() OVER函数来实现这一功能。这个函数可以给每一行数据分配一个唯一的行号，方便后续处理。

1. ROW_NUMBER() OVER函数的基本使用

ROW_NUMBER() OVER函数用于返回某个排序字段的行号。下面是使用ROW_NUMBER() OVER函数给一个表中的记录分配行号的简单案例。

首先，我们假设有以下数据表：

CREATE TABLE [testTable]

(
    [ID] INT,
    [Name] VARCHAR(20),
    [Age] INT
);
INSERT INTO [testTable] VALUES(1, 'Tom', 18);
INSERT INTO [testTable] VALUES(2, 'Jack', 20);
INSERT INTO [testTable] VALUES(3, 'Lucy', 22);
INSERT INTO [testTable] VALUES(4, 'Lily', 24);

我们可以使用ROW_NUMBER() OVER函数为每一行数据分配一个行号，如下：

SELECT ROW_NUMBER() OVER(ORDER BY [ID]) AS [RowNumber], * FROM [testTable];

这里我们使用ORDER BY语句对ID进行排序，然后ROW_NUMBER() OVER函数会把排序后的每一行数据分配一个唯一的行号。执行以上SQL语句后，结果如下：

2. 给不同的分组分配行号

ROW_NUMBER() OVER函数可以让我们给某个字段的不同分组分配不同的行号。以下是一个例子。

假设我们有以下的数据表，其中每个人都有不同的年龄和性别：

CREATE TABLE [testTable2]

(
    [Name] VARCHAR(20),
    [Age] INT,
    [Gender] VARCHAR(10)
);
INSERT INTO [testTable2] VALUES('Tom', 18, 'Male');
INSERT INTO [testTable2] VALUES('Jack', 20, 'Male');
INSERT INTO [testTable2] VALUES('Lucy', 22, 'Female');
INSERT INTO [testTable2] VALUES('Lily', 24, 'Female');
INSERT INTO [testTable2] VALUES('Mike', 26, 'Male');
INSERT INTO [testTable2] VALUES('Mary', 28, 'Female');
INSERT INTO [testTable2] VALUES('Tony', 30, 'Male');

现在，我们想要给他们按照年龄和性别分组，并且为每一组分配一个行号。以下是实现这一功能的SQL语句：

SELECT ROW_NUMBER() OVER(PARTITION BY [Gender] ORDER BY [Age]) AS [RowNumber], * FROM [testTable2];

这里我们使用了PARTITION BY语句对Gender字段进行分组，然后使用ORDER BY语句对Age字段进行排序。这样，ROW_NUMBER() OVER函数就会给每个分组内的数据分配一个唯一的行号。执行以上SQL语句后，结果如下：

3. 使用ROW_NUMBER() OVER函数进行排名

使用ROW_NUMBER() OVER函数还可以方便的进行排名操作。排名是指按照某个字段进行排序后，给每一行数据一个排名。以下是使用ROW_NUMBER() OVER函数进行排名的例子。

首先，我们假设有如下数据表：

CREATE TABLE [testTable3]

(
    [ID] INT,
    [Score] INT
);
INSERT INTO [testTable3] VALUES(1, 80);
INSERT INTO [testTable3] VALUES(2, 90);
INSERT INTO [testTable3] VALUES(3, 95);
INSERT INTO [testTable3] VALUES(4, 85);
INSERT INTO [testTable3] VALUES(5, 90);

我们可以使用ROW_NUMBER() OVER函数进行排名，如下：

SELECT [ID], [Score], ROW_NUMBER() OVER(ORDER BY [Score] DESC) AS [Rank] FROM [testTable3];

这里我们使用ORDER BY语句对Score字段进行降序排序，然后ROW_NUMBER() OVER函数会对排序后的每一行数据进行排名。执行以上SQL语句后，结果如下：