Group sorting function — row_ number()

Time:2021-6-14

1. MySQL 8.0 or above

Usage 1: no grouping sort

Row_ Number() over (order by field DESC)
For example: row_ Number () over (order by DESC)
All students are ranked from high to low, regardless of class
Usage 2: Group sort
ROW_ Number () over (division by field 1 order by field 2 DESC)
It means to group according to field 1 and sort according to field 2 inside the group. The value calculated by this function represents the order number after sorting inside each group
For example: row_ Number () over (division by class order by student grade DESC)
It means to group according to “class” and sort according to “student achievement” in each “class”. The value calculated by this function represents the value after sorting in each group
Sequence number
Explanation:
ROW_ NUMBER( )   It has the function of numbering
partition by   Partition the same data
order by     Make the data sort in a certain order

2. MySQL version 5.7

Usage 1: no grouping sort

For example: calculate the sales of sales personnel, sort the results from high to low, and the query results should include the sales ranking

SET @rank := 0;
SELECT
A.*,
@rank := @rank + 1 AS rank 
FROM
( SELECT sales_name, sum( sales ) FROM spm_order

GROUP BY sales_name

ORDER BY sum( sales ) DESC ) A

Usage 2: Group sort

Example: calculate the sales volume of sales personnel in different cities;

Requirements: results based onsalesmanstayDifferent citiesAnd the query results should includegroupingranking

SET @r := 0,
@type := ”;
SELECT
 @r :=
CASE WHEN @type = a.sales_name THEN
@r + 1 ELSE 1
END AS rowNum,
 @type := a.sales_name AS type,
  a.*
FROM
( SELECT sales_name, city, sum( sales ) FROM spm_order

GROUP BY sales_name, city

ORDER BY sales_name, sum( sales ) DESC ) a;