Implementation process of row_number in MySQL

1. Background

Generally, in a data warehouse environment, we can easily use the row_number function to group data according to a certain dimension to achieve the effect of sorting the data numbers in each group. As shown in the figure below, this is the effect diagram generated in the MySQL environment. The grouping here is based on lcid, and num is equivalent to the effect achieved by the row_number function:

2. Implementation process

1. Set mysql variables

Set up two variables

set @row_number:=0; --Generate row_number sequence number according to the judgment result of lcid_no set @lcid_no:= 0; --Used to obtain the lcid column data of each row, and then compare it with the lcid data of the previous row. If they are the same, it will increase by 1, otherwise it will be 1

2. Use case when

SELECT @row_number:=CASE
    WHEN @lcid_no = s.lcid THEN @row_number + 1 
    ELSE 1
    END AS num,
    @lcid_no:=s.lcid AS lcid,
	  s.lcid
FROM r_qcloud_approval_fh_d s,(select @orw_number:=0,@lcid_no:=0) t
ORDER BY s.lcid;

3. Process analysis <br /> If there are multiple identical data
1) Under the initial condition, the cursor points to the first data. At this time, lcid_no = 0, lcid_no is not equal to lcid, so row_number = 1
2) The cursor points to the second data, lcid_no = lcid of the previous data, because the lcid of the previous data = the lcid of the current row, so row_number = 2
No duplicate data
1) Under the initial condition, the cursor points to the first data. At this time, lcid_no = 0, lcid_no is not equal to lcid, so row_number = 1

3. Usage scenarios

In a relational database like MySQL, there is no row_number function.

This is the end of this article about the implementation process of row_number in MySQL. For more relevant content about row_number in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!