The following explanation will be based on this employee2 table:
mysql> SELECT * FROM employee2; +----+-----------+------+---------+---------+ | id | name | age | salary | dept_id | +----+-----------+------+---------+---------+ | 3 | Xiao Xiao | 29 | 30000.0 | 1 | | 4 | Xiaodong | 30 | 40000.0 | 2 | | 6 | Xiao Fei | 24 | 23456.0 | 3 | | 7 | Xiaofei | 30 | 15000.0 | 4 | | 8 | Kobayashi | 23 | 24000.0 | NULL | | 10 | Small five | 20 | 4500.0 | NULL | | 11 | Zhang Shan | 24 | 40000.0 | 1 | | 12 | Xiao Xiao | 28 | 35000.0 | 2 | | 13 | Li Si | 23 | 50000.0 | 1 | | 17 | Wang Wu | 24 | 56000.0 | 2 | | 18 | Pig fart | 2 | 56000.0 | 2 | | 19 | Xiaoyu | 25 | 58000.0 | 1 | | 21 | Xiao Zhang | 23 | 50000.0 | 1 | | 22 | Xiao Hu | 25 | 25000.0 | 2 | | 96 | Xiao Xiao | 19 | 35000.0 | 1 | | 97 | Kobayashi | 20 | 20000.0 | 2 | +----+-----------+------+---------+---------+ 16 rows in set (0.00 sec)
The window function is OVER(), in which the corresponding clauses are PARTITION BY and ORDER BY clauses, so the form is:
- OVER(): in this case, it is an empty clause. The effect is the same as that without using the OVER() function. It is used for the window composed of all the data in this table.
mysql> SELECT
-> name,
-> salary,
-> MAX(salary) OVER() AS max_salary -- It acts on an entire window, and the data in all data is returned at this time MAX(salary),Indicates the maximum salary of all employees
-> FROM employee2;
+-----------+---------+------------+
| name | salary | max_salary |
+-----------+---------+------------+
| Xiao Xiao | 30000.0 | 58000.0 |
| Xiaodong | 40000.0 | 58000.0 |
| Xiao Fei | 23456.0 | 58000.0 |
| Xiaofei | 15000.0 | 58000.0 |
| Kobayashi | 24000.0 | 58000.0 |
| Small five | 4500.0 | 58000.0 |
| Zhang Shan | 40000.0 | 58000.0 |
| Xiao Xiao | 35000.0 | 58000.0 |
| Li Si | 50000.0 | 58000.0 |
| Wang Wu | 56000.0 | 58000.0 |
| Pig fart | 56000.0 | 58000.0 |
| Xiaoyu | 58000.0 | 58000.0 |
| Xiao Zhang | 50000.0 | 58000.0 |
| Xiao Hu | 25000.0 | 58000.0 |
| Xiao Xiao | 35000.0 | 58000.0 |
| Kobayashi | 20000.0 | 58000.0 |
+-----------+---------+------------+
16 rows in set (0.00 sec)
mysql> SELECT
-> name,
-> salary,
-> MAX(salary) OVER() -- Get the of all employees with department 1 name,salary And the maximum salary in this department
-> FROM employee2
-> WHERE dept_id = 1;
+--------+---------+--------------------+
| name | salary | MAX(salary) OVER() |
+--------+---------+--------------------+
| Xiao Xiao | 30000.0 | 58000.0 |
| Zhang Shan | 40000.0 | 58000.0 |
| Li Si | 50000.0 | 58000.0 |
| Xiaoyu | 58000.0 | 58000.0 |
| Xiao Zhang | 50000.0 | 58000.0 |
| Xiao Xiao | 35000.0 | 58000.0 |
+--------+---------+--------------------+
6 rows in set (0.00 sec)
- Over (PARTITION BY, yyy1,yyy2,yyy3): contains the PARTITION BY clause. At this time, windows will be divided according to the whole composed of yyy1, yyy2 and yyy3 columns. Only when the whole composed of these columns is the same, they will be in the same window.
mysql> SELECT
-> name,
-> salary,
-> MAX(salary) OVER(PARTITION BY dept_id) AS dept_max_salary -- Used PARTITION BY ，Thus according to dept_id Group, and then get the maximum value of each group
-> FROM employee2;
+-----------+---------+-----------------+
| name | salary | dept_max_salary |
+-----------+---------+-----------------+
| Kobayashi | 24000.0 | 24000.0 | --| Group as NULL of
| Small five | 4500.0 | 24000.0 | --|
| Xiao Xiao | 30000.0 | 58000.0 | -----|
| Zhang Shan | 40000.0 | 58000.0 |
| Li Si | 50000.0 | 58000.0 | -- Group as dept_id = 1 of
| Xiaoyu | 58000.0 | 58000.0 |
| Xiao Zhang | 50000.0 | 58000.0 |
| Xiao Xiao | 35000.0 | 58000.0 | -----|
| Xiaodong | 40000.0 | 56000.0 | ---------|
| Xiao Xiao | 35000.0 | 56000.0 |
| Wang Wu | 56000.0 | 56000.0 |
| Pig fart | 56000.0 | 56000.0 | -- Group as dept_id = 2 of
| Xiao Hu | 25000.0 | 56000.0 |
| Kobayashi | 20000.0 | 56000.0 | ---------|
| Xiao Fei | 23456.0 | 23456.0 | -- ------------| Group as dept_id = 3 of
| Xiaofei | 15000.0 | 15000.0 | -- --------------| Group as dept_id = 4 of
+-----------+---------+-----------------+
16 rows in set (0.00 sec)
- Over (ORDER BY yyy1, yyy2, yyy3, ASC \ DESC): using the ORDER BY clause, the window will be sorted in the ascending / descending order corresponding to yyy1. If yyy1 is the same, it will be sorted according to yyy2 (the same as the usage of ORDER BY). At this time, not only the sorting operation will be carried out, but also the accumulation operation will be carried out if SUM is used together with it, that is, the value is the value corresponding to the current window plus the previous window. Note that this is a window, not a row. Therefore, if you are in the same window at the same time, the operation of retrograde accumulation will not be carried out, but only in different windows The accumulation operation will be carried out.
mysql> SELECT
-> name,
-> salary,
-> SUM(salary) OVER(ORDER BY salary) AS already_paid_salary -- utilize ORDER BY ，The window will follow salary Sort ascending, then call SUM Aggregate function to accumulate different windows
-> FROM employee2;
+-----------+---------+---------------------+
| name | salary | already_paid_salary |
+-----------+---------+---------------------+
| Small five | 4500.0 | 4500.0 |
| Xiaofei | 15000.0 | 19500.0 |
| Kobayashi | 20000.0 | 39500.0 |
| Xiao Fei | 23456.0 | 62956.0 |
| Kobayashi | 24000.0 | 86956.0 |
| Xiao Hu | 25000.0 | 111956.0 |
| Xiao Xiao | 30000.0 | 141956.0 |
| Xiao Xiao | 35000.0 | 211956.0 | -- -----| The two groups are in the same window, and the already_paid_salary
| Xiao Xiao | 35000.0 | 211956.0 | -- -----| = (35000 * 2) (Of the current window) + 141956(Previous window)
| Xiaodong | 40000.0 | 291956.0 | -- ---| The two groups are in the same window, and the already_paid_salary
| Zhang Shan | 40000.0 | 291956.0 | -- ---| = (40000 * 2)(Current window) + 211956(Previous window)
| Li Si | 50000.0 | 391956.0 | -- | The reason is the same as above
| Xiao Zhang | 50000.0 | 391956.0 | -- |
| Wang Wu | 56000.0 | 503956.0 | -- ------|The reason is the same as above
| Pig fart | 56000.0 | 503956.0 | -- ------|
| Xiaoyu | 58000.0 | 561956.0 |
+-----------+---------+---------------------+
16 rows in set (0.00 sec)
mysql> SELECT
-> name,
-> salary,
-> SUM(salary) OVER(ORDER BY name) -- Each window will be based on name Sort in ascending order. At this time, each different window will perform cumulative operation
-> FROM employee2;
+-----------+---------+---------------------------------+
| name | salary | SUM(salary) OVER(ORDER BY name) |
+-----------+---------+---------------------------------+
| Xiaodong | 40000.0 | 40000.0 |
| Small five | 4500.0 | 44500.0 |
| Xiao Zhang | 50000.0 | 94500.0 |
| Kobayashi | 24000.0 | 138500.0 | -- |The two groups are in the same window, so the corresponding value is(24000 + 20000)
| Kobayashi | 20000.0 | 138500.0 | -- | (Of the current window) + 94500(Previous window)
| Xiaoyu | 58000.0 | 196500.0 |
| Xiao Xiao | 30000.0 | 296500.0 | -- ---|The three groups are in the same window, so the corresponding value is(30000
| Xiao Xiao | 35000.0 | 296500.0 | -- + 35000 + 35000)(Of the current window) + 196500(before
| Xiao Xiao | 35000.0 | 296500.0 | -- ---|Windowed)
| Xiao Hu | 25000.0 | 321500.0 |
| Xiao Fei | 23456.0 | 344956.0 |
| Zhang Shan | 40000.0 | 384956.0 |
| Xiaofei | 15000.0 | 399956.0 |
| Li Si | 50000.0 | 449956.0 |
| Pig fart | 56000.0 | 505956.0 |
| Wang Wu | 56000.0 | 561956.0 |
+-----------+---------+---------------------------------+
16 rows in set (0.00 sec)
- OVER(PARTITION BY yyy ORDER BY zzz ASC\DESC): according to PARTITION BY, it means grouping according to yyy. Then, in each grouping, ORDER BY clause will be used to sort according to zzz. It should be noted that if zzz and yyy are the same, the effect is equivalent to OVER(PARTITION BY yyy), which is the same as without ORDER BY clause, because they are in the same group.
mysql> SELECT
-> name,
-> salary,
-> SUM(salary) OVER(PARTITION BY dept_id)
-> FROM employee2;
+-----------+---------+----------------------------------------+
| name | salary | SUM(salary) OVER(PARTITION BY dept_id) |
+-----------+---------+----------------------------------------+
| Kobayashi | 24000.0 | 28500.0 | -- | Group as dept_id = NULL of
| Small five | 4500.0 | 28500.0 | -- |
| Xiao Xiao | 30000.0 | 263000.0 | ------|
| Zhang Shan | 40000.0 | 263000.0 |
| Li Si | 50000.0 | 263000.0 |
| Xiaoyu | 58000.0 | 263000.0 | -- Group as dept_id = 1 of
| Xiao Zhang | 50000.0 | 263000.0 |
| Xiao Xiao | 35000.0 | 263000.0 | ------|
| Xiaodong | 40000.0 | 232000.0 | --------|
| Xiao Xiao | 35000.0 | 232000.0 |
| Wang Wu | 56000.0 | 232000.0 |
| Pig fart | 56000.0 | 232000.0 | -- Group as dept_id = 2 of
| Xiao Hu | 25000.0 | 232000.0 |
| Kobayashi | 20000.0 | 232000.0 | --------|
| Xiao Fei | 23456.0 | 23456.0 | -- ---------| Group as dept_id = 3 of
| Xiaofei | 15000.0 | 15000.0 | -- ------------| Group as dept_id = 4 of
+-----------+---------+----------------------------------------+
16 rows in set (0.00 sec)
mysql> SELECT
-> name,
-> salary,
-> SUM(salary) OVER(PARTITION BY dept_id ORDER BY dept_id)
-> FROM employee2;
+-----------+---------+---------------------------------------------------------+
| name | salary | SUM(salary) OVER(PARTITION BY dept_id ORDER BY dept_id) |
+-----------+---------+---------------------------------------------------------+
| Kobayashi | 24000.0 | 28500.0 |
| Small five | 4500.0 | 28500.0 |
| Xiao Xiao | 30000.0 | 263000.0 |
| Zhang Shan | 40000.0 | 263000.0 |
| Li Si | 50000.0 | 263000.0 |
| Xiaoyu | 58000.0 | 263000.0 |
| Xiao Zhang | 50000.0 | 263000.0 |
| Xiao Xiao | 35000.0 | 263000.0 |
| Xiaodong | 40000.0 | 232000.0 |
| Xiao Xiao | 35000.0 | 232000.0 |
| Wang Wu | 56000.0 | 232000.0 |
| Pig fart | 56000.0 | 232000.0 |
| Xiao Hu | 25000.0 | 232000.0 |
| Kobayashi | 20000.0 | 232000.0 |
| Xiao Fei | 23456.0 | 23456.0 |
| Xiaofei | 15000.0 | 15000.0 |
+-----------+---------+---------------------------------------------------------+
16 rows in set (0.00 sec)
The window function can be used with SUM()\AVG()\COUNT()\MAX()\MIN():
These functions have some characteristics. If there must be parameters in the brackets of AVG()\COUNT()\MAX()\MIN(), they are used to count the corresponding values of a column. If this column contains rows with NULL values, the rows with NULL values will be ignored, while COUNT() is special. If it is count (*), the rows with NULL values will not be ignored to count the number of rows in this table, Otherwise, if COUNT(column) counts the number of rows in a column, NULL rows will be ignored.
If you need to specify the output format of decimals such as AVG(), you need to use the following functions:
- FORMAT(xxx,yyy,zzz) specifies that xxx has yyy decimal places. However, this function has a feature that every three numbers in the integer part will be separated by a separator (calculated from the first number to the left of the decimal point). If you do not write zzz this parameter, that is, there are only two parameters, you will use, as the separator. For example, 45000, if FORMAT(45000, 2) is used, the final result is 45000.00, and then, for example, FORMAT(45000,4), the result is 45000.0000
# Use FORMAT to specify how many decimal points are reserved at the end of the decimal point, and calculate from the first digit to the left of the decimal point, every three digits
# There will be a separator. Note that the original FORMAT() has three parameters. If you don't write zzz this parameter, it will use ',' by default
# Is a separator
mysql> SELECT
-> name,
-> FORMAT(salary,4)
-> FROM employee2;
+-----------+------------------+
| name | FORMAT(salary,4) |
+-----------+------------------+
| Xiao Xiao | 30,000.0000 |
| Xiaodong | 40,000.0000 |
| Xiao Fei | 23,456.0000 |
| Xiaofei | 15,000.0000 |
| Kobayashi | 24,000.0000 |
| Small five | 4,500.0000 |
| Zhang Shan | 40,000.0000 |
| Xiao Xiao | 35,000.0000 |
| Li Si | 50,000.0000 |
| Wang Wu | 56,000.0000 |
| Pig fart | 56,000.0000 |
| Xiaoyu | 58,000.0000 |
| Xiao Zhang | 50,000.0000 |
| Xiao Hu | 25,000.0000 |
| Xiao Xiao | 35,000.0000 |
| Kobayashi | 20,000.0000 |
+-----------+------------------+
16 rows in set (0.00 sec)
- CAST(xxx AS decimal(12,yyy)): Specifies that xxx has yyy decimals. The function is the same as that of CONVERT(). It specifies that xxx has yyy decimals, but unlike FORMAT(), it does not separate every three numbers with commas, for example, 45000. If it specifies to output three decimals, CONVERT(45000,DECIMAL(12,3)) will output 45000.0 without commas
mysql> SELECT
-> name,
-> CAST(salary AS DECIMAL(12,3)) -- use CAST，This is equivalent to CONVERT Similarly, specify how many decimals there are and no delimiters appear
-> FROM employee2;
+-----------+-------------------------------+
| name | CAST(salary AS DECIMAL(12,3)) |
+-----------+-------------------------------+
| Xiao Xiao | 30000.000 |
| Xiaodong | 40000.000 |
| Xiao Fei | 23456.000 |
| Xiaofei | 15000.000 |
| Kobayashi | 24000.000 |
| Small five | 4500.000 |
| Zhang Shan | 40000.000 |
| Xiao Xiao | 35000.000 |
| Li Si | 50000.000 |
| Wang Wu | 56000.000 |
| Pig fart | 56000.000 |
| Xiaoyu | 58000.000 |
| Xiao Zhang | 50000.000 |
| Xiao Hu | 25000.000 |
| Xiao Xiao | 35000.000 |
| Kobayashi | 20000.000 |
+-----------+-------------------------------+
16 rows in set (0.00 sec)
- CONVERT(xxx,DECIMAL(12,yyy)): Specifies that xxx has yyy decimals, but unlike FORMAT(), it does not separate every three numbers with commas. For example, 45000 specifies that 3 decimals are output, then CONVERT(45000,DECIMAL(12,3)) will output 45000.0 without commas
# Using CONVERT, when specifying how many decimals there are, there will be no separator such as comma, that is, the first number to the left of the decimal point
# At the beginning of the word, the separator will not appear in every three numbers as in FORMAT
mysql> SELECT
-> name,
-> CONVERT(salary,DECIMAL(12,3))
-> FROM employee2;
+-----------+-------------------------------+
| name | CONVERT(salary,DECIMAL(12,3)) |
+-----------+-------------------------------+
| Xiao Xiao | 30000.000 |
| Xiaodong | 40000.000 |
| Xiao Fei | 23456.000 |
| Xiaofei | 15000.000 |
| Kobayashi | 24000.000 |
| Small five | 4500.000 |
| Zhang Shan | 40000.000 |
| Xiao Xiao | 35000.000 |
| Li Si | 50000.000 |
| Wang Wu | 56000.000 |
| Pig fart | 56000.000 |
| Xiaoyu | 58000.000 |
| Xiao Zhang | 50000.000 |
| Xiao Hu | 25000.000 |
| Xiao Xiao | 35000.000 |
| Kobayashi | 20000.000 |
+-----------+-------------------------------+
16 rows in set (0.00 sec)
In addition, in addition to the separator difference between the above three functions, there is also the aspect of ORDER BY. Because FORMAT obtains a string, when using ORDER BY, it is sorted based on dictionary order, while CONVERT\CAST obtains a number. Therefore, when using ORDER BY, it is still sorted according to numbers.
# Use CAST, and then use this column for sorting output. Since CAST gets a number, use ORDER BY
# It is sorted according to the size of the number
mysql> SELECT
-> name,
-> CAST(salary AS DECIMAL(12,3)) AS cast_salary
-> FROM employee2
-> ORDER BY cast_salary;
+-----------+-------------+
| name | cast_salary |
+-----------+-------------+
| Small five | 4500.000 |
| Xiaofei | 15000.000 |
| Kobayashi | 20000.000 |
| Xiao Fei | 23456.000 |
| Kobayashi | 24000.000 |
| Xiao Hu | 25000.000 |
| Xiao Xiao | 30000.000 |
| Xiao Xiao | 35000.000 |
| Xiao Xiao | 35000.000 |
| Xiaodong | 40000.000 |
| Zhang Shan | 40000.000 |
| Li Si | 50000.000 |
| Xiao Zhang | 50000.000 |
| Wang Wu | 56000.000 |
| Pig fart | 56000.000 |
| Xiaoyu | 58000.000 |
+-----------+-------------+
16 rows in set (0.00 sec)
# Use FORMAT, and then use this column for sorting output. Since FORMAT gets a string, use ORDER BY
# It is sorted according to the dictionary order
mysql> SELECT
-> name,
-> FORMAT(salary,3) AS format_salary
-> FROM employee2
-> ORDER BY format_salary;
+-----------+---------------+
| name | format_salary |
+-----------+---------------+
| Xiaofei | 15,000.000 |
| Kobayashi | 20,000.000 |
| Xiao Fei | 23,456.000 |
| Kobayashi | 24,000.000 |
| Xiao Hu | 25,000.000 |
| Xiao Xiao | 30,000.000 |
| Xiao Xiao | 35,000.000 |
| Xiao Xiao | 35,000.000 |
| Small five | 4,500.000 |
| Xiaodong | 40,000.000 |
| Zhang Shan | 40,000.000 |
| Li Si | 50,000.000 |
| Xiao Zhang | 50,000.000 |
| Wang Wu | 56,000.000 |
| Pig fart | 56,000.000 |
| Xiaoyu | 58,000.000 |
+-----------+---------------+
16 rows in set (0.00 sec)
# Use CONVERT, and then use this column for sorting output. Since CONVERT gets a number, use ORDER BY
# It is sorted according to the size of the number
mysql> SELECT
-> name,
-> CONVERT(salary,DECIMAL(12,3)) AS convert_salary
-> FROM employee2
-> ORDER BY convert_salary;
+-----------+----------------+
| name | convert_salary |
+-----------+----------------+
| Small five | 4500.000 |
| Xiaofei | 15000.000 |
| Kobayashi | 20000.000 |
| Xiao Fei | 23456.000 |
| Kobayashi | 24000.000 |
| Xiao Hu | 25000.000 |
| Xiao Xiao | 30000.000 |
| Xiao Xiao | 35000.000 |
| Xiao Xiao | 35000.000 |
| Xiaodong | 40000.000 |
| Zhang Shan | 40000.000 |
| Li Si | 50000.000 |
| Xiao Zhang | 50000.000 |
| Wang Wu | 56000.000 |
| Pig fart | 56000.000 |
| Xiaoyu | 58000.000 |
+-----------+----------------+
16 rows in set (0.00 sec)
The format of output is discussed in this topic: Test score (I)
It is worth mentioning that MAX()\MIN() can not only solve the maximum value of numerical value and date, but also solve the maximum value of text.
Here we mainly talk about the use of SUM() and window functions: SUM(xxx) OVER(PARTITION BY yyy ORDER BY zzz): This is grouped according to yyy, so it is divided into multiple windows. These windows are sorted according to zzz, and then each window will accumulate XXX continuously.
The following problem is applied to SUM() function and window function OVER(): Count the cumulative sum of salary and running_total
Window functions can also be used with sort functions
- ROW_NUMBER() OVER(): directly indicates the row number, and there will be no juxtaposition
- DENSE_RANK() OVER(): parallel and continuous
- RANK() OVER(): parallel discontinuities
# ROW_NUMBER() OVER() directly indicates the row number
mysql> SELECT
-> name,
-> salary,
-> ROW_NUMBER() OVER(ORDER BY salary DESC)
-> FROM employee2;
+-----------+---------+-----------------------------------------+
| name | salary | ROW_NUMBER() OVER(ORDER BY salary DESC) |
+-----------+---------+-----------------------------------------+
| Xiaoyu | 58000.0 | 1 |
| Wang Wu | 56000.0 | 2 |
| Pig fart | 56000.0 | 3 |
| Li Si | 50000.0 | 4 |
| Xiao Zhang | 50000.0 | 5 |
| Xiaodong | 40000.0 | 6 |
| Zhang Shan | 40000.0 | 7 |
| Xiao Xiao | 35000.0 | 8 |
| Xiao Xiao | 35000.0 | 9 |
| Xiao Xiao | 30000.0 | 10 |
| Xiao Hu | 25000.0 | 11 |
| Kobayashi | 24000.0 | 12 |
| Xiao Fei | 23456.0 | 13 |
| Kobayashi | 20000.0 | 14 |
| Xiaofei | 15000.0 | 15 |
| Small five | 4500.0 | 16 |
+-----------+---------+-----------------------------------------+
16 rows in set (0.00 sec)
# RANK() OVER() indicates juxtaposition, but not contiguity
mysql> SELECT
-> name,
-> salary,
-> RANK() OVER(ORDER BY salary DESC) -- according to salary Sort in descending order
-> FROM employee2;
+-----------+---------+-----------------------------------+
| name | salary | RANK() OVER(ORDER BY salary DESC) |
+-----------+---------+-----------------------------------+
| Xiaoyu | 58000.0 | 1 |
| Wang Wu | 56000.0 | 2 | -- --| These two groups are in the second place, but they will not be continuous, so the next group is
| Pig fart | 56000.0 | 2 | -- --| It started at 4
| Li Si | 50000.0 | 4 |
| Xiao Zhang | 50000.0 | 4 |
| Xiaodong | 40000.0 | 6 |
| Zhang Shan | 40000.0 | 6 |
| Xiao Xiao | 35000.0 | 8 |
| Xiao Xiao | 35000.0 | 8 |
| Xiao Xiao | 30000.0 | 10 |
| Xiao Hu | 25000.0 | 11 |
| Kobayashi | 24000.0 | 12 |
| Xiao Fei | 23456.0 | 13 |
| Kobayashi | 20000.0 | 14 |
| Xiaofei | 15000.0 | 15 |
| Small five | 4500.0 | 16 |
+-----------+---------+-----------------------------------+
16 rows in set (0.00 sec)
# DENSE_RANK() OVER() sort in parallel
mysql> SELECT
-> name,
-> salary,
-> DENSE_RANK() OVER(ORDER BY salary DESC)
-> FROM employee2;
+-----------+---------+-----------------------------------------+
| name | salary | DENSE_RANK() OVER(ORDER BY salary DESC) |
+-----------+---------+-----------------------------------------+
| Xiaoyu | 58000.0 | 1 |
| Wang Wu | 56000.0 | 2 | -- |The two groups are tied for the second place and are ranked consecutively
| Pig fart | 56000.0 | 2 | -- |So the next group starts with 3
| Li Si | 50000.0 | 3 |
| Xiao Zhang | 50000.0 | 3 |
| Xiaodong | 40000.0 | 4 |
| Zhang Shan | 40000.0 | 4 |
| Xiao Xiao | 35000.0 | 5 |
| Xiao Xiao | 35000.0 | 5 |
| Xiao Xiao | 30000.0 | 6 |
| Xiao Hu | 25000.0 | 7 |
| Kobayashi | 24000.0 | 8 |
| Xiao Fei | 23456.0 | 9 |
| Kobayashi | 20000.0 | 10 |
| Xiaofei | 15000.0 | 11 |
| Small five | 4500.0 | 12 |
+-----------+---------+-----------------------------------------+
16 rows in set (0.00 sec)
In addition, the window function can also be used with other functions, which will not be listed here.
Using the exercise corresponding to the sorting function: Ranking of questions passed by brushing
reference material:
WHAT IS the MySQL OVER clause？