Using row_ The method of assigning line number to every record in SQL database by number() over function

Time:2020-10-26

Since SQL Server 2005, a new function row has been added_ One of its great functions is to add a column line number starting from 1 in the data table, which greatly replaces all redundant code to generate line numbers. Here’s how to use row_ The number() function.

Suppose there are the following tables in the database:

Copy codeThe code is as follows:
id    name
1    aa
3    bb
4    cc

After executing the following SQL statement, you will get the following results:

SELECT ROW_ Number() over (order by ID) as rownum, * from table name

Copy codeThe code is as follows:
rowNum    id    name
1                 1    aa
2                 3    bb
3                 4    cc