SQL uses ROW_NUMBER() OVER function to generate sequence number

Syntax: ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
Simply put, ROW_NUMBER() starts from 1 and returns a number for each grouped record. Here, ROW_NUMBER() OVER (ORDER BY CYLH DESC) first sorts the xlh column in descending order, and then returns a serial number for each CYLH record after the descending order.

Example:

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

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

例子：

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,20281997,10.50)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000010,20281996,10.50)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000008,20281995,0.00)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000006,20281994,9.50)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000004,20281993,5.50)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000001,20281992,10.50)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000002,20281992,10.50)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000007,20217280,0.00)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000009,20172458,5.50)
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000005,20121813,0.00)

Execute the script to automatically generate line numbers and sort by CYLH (swipe 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 window functions such as over , group by and sorting in over are executed later than the execution of " where , group by, order by ".

This is the end of this article about using SQL ROW_NUMBER() OVER function to generate serial numbers. For more information about using SQL ROW_NUMBER() OVER to generate serial numbers, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!