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

Implement QR code scanning function through Vue

hint This plug-in can only be accessed under the ...

In IIS 7.5, HTML supports the include function like SHTML (add module mapping)

When I first started, I found a lot of errors. In...

Implementing a random roll caller based on JavaScript

This article shares the specific code of JavaScri...

A brief understanding of the relevant locks in MySQL

This article is mainly to take you to quickly und...

A brief discussion on the correct posture of Tomcat memory configuration

1. Background Although I have read many blogs or ...

A complete guide to the Docker command line (18 things you have to know)

Preface A Docker image consists of a Dockerfile a...

How to load third-party component libraries on demand in Vue3

Preface Take Element Plus as an example to config...

Docker solves the problem that the terminal cannot input Chinese

Preface: One day, I built a MySQL service in Dock...

ThingJS particle effects to achieve rain and snow effects with one click

Table of contents 1. Particle Effects 2. Load the...

Example of using docker compose to build a consul cluster environment

Basic concepts of consul Server mode and client m...

React's method of realizing secondary linkage

This article shares the specific code of React to...

Sliding menu implemented with CSS3

Result:Implementation code: <!DOCTYPE html>...

The difference and execution method of select count() and select count(1)

Count(*) or Count(1) or Count([column]) are perha...