SQL uses ROW_NUMBER() OVER function to generate sequence number

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!

You may also be interested in:
  • Comparison of two methods for parsing database paging (comparison between row_number()over() and top)
  • SQL Server automatically generates a serial number with a date plus a number

<<:  What are the attributes of the JSscript tag

>>:  Introduction and use of five controllers in K8S

Recommend

Implementing a puzzle game with js

This article shares the specific code of js to im...

How to use a game controller in CocosCreator

Table of contents 1. Scene layout 2. Add a handle...

In-depth understanding of Vue transition and animation

1. When inserting, updating, or removing DOM elem...

Vue realizes the progress bar change effect

This article uses Vue to simply implement the cha...

Detailed explanation of the use of JavaScript functions

Table of contents 1. Declare a function 2. Callin...

MySQL 5.7.17 installation and use graphic tutorial

MySQL is a relational database management system ...

Vue implements anchor positioning function

This article example shares the specific code of ...

Teach you how to install docker on windows 10 home edition

When I wrote the Redis book and the Spring Cloud ...

Linux uses dual network card bond and screwdriver interface

What is bond NIC bond is a technology that is com...

Eight rules for effective web forms

If you're collecting information from your us...

Things You Don’t Know About the CSS ::before and ::after Pseudo-Elements

CSS has two pseudo-classes that are not commonly ...

JavaScript implementation of classic snake game

This article shares the specific code of JavaScri...