How SQL uses the ROW_NUMBER () OVER function to generate serial numbers

This article introduces how SQL uses the ROW_NUMBER () OVER function to generate serial numbers, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Syntax: ROW_NUMBER () OVER (PARTITION BY COLUMN ORDER BY COLUMN)

To put it simply, ROW_NUMBER () starts at 1 and returns a number for each grouped record, where ROW_NUMBER () OVER (ORDER BY CYLH DESC) first descends the xlh column, and then returns a sequence number for each CYLH record after descending.

Example:

Analysis: ROW_NUMBER () OVER (PARTITION BY COL1 ORDER BY COL2) means grouping according to COL1, sorting according to COL2 within the grouping, and the value calculated by this function represents the sorted sequence number within each group (continuous and unique within the group)

It can also be used like this: ROW_NUMBER () OVER (ORDER BY COL2)

Example:

Create a test table and insert test data

CREATE TABLE TEST_ROW_NUMBER_01 (CMZH varchar (10) not null, CYLH varchar (10) null, MJE money null) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000011) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000010) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000008) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000006) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000004) VALUES (20281993) INSERT INTO TEST_ROW_NUMBER_01 MJE) VALUES (2106000001) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000002) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000007) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000009 20172458) INSERT INTO TEST_ROW_NUMBER_01 (CMZH,CYLH,MJE) VALUES (2106000005)

Execute the script to automatically generate line numbers and sort by CYLH (slide to view the code)

SELECT ROW_NUMBER () OVER (ORDER BY CYLH DESC) AS ROWNUM,* FROM TEST_ROW_NUMBER_01

The results are as follows:

Note: when using windowing functions such as over, the execution of grouping and sorting in over is later than that of "where,group by,order by".

About SQL how to use ROW_NUMBER () OVER function to generate serial number to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.