mysql实现分组排序[转]

mysql8支持窗口函数，直接
select emp_no, dept_no, rank() over(partition by dept_no order by emp_no) as cc from dept_manager;
具体参考官方文档window-functions-usage.html

MySQL-to achieve ORACLE-ROW_NUMBER () over (partition by) packet sorting capabilities.
Not provide a similar MYSQL ORACLE in OVER () such a wealth of analysis functions. Need to implement such a function in MySQL, we can only use some flexible approach:

First of all, we create instance data:

(2) the identification of needs: grouped according to the department, the staff in the department ranked by salary ranking.
Showing results are expected to be as follows:

+ ------- + -------- + ---------- + ------ +
| Empid | deptid | salary | rank |
+ ------- + -------- + ---------- + ------ +
| 1 | 10 | 5500.00 | 1 |
| 2 | 10 | 4500.00 | 2 |
| 4 | 20 | 4800.00 | 1 |
| 3 | 20 | 1900.00 | 2 |
| 7 | 40 | 44500.00 | 1 |
| 6 | 40 | 14500.00 | 2 |
| 5 | 40 | 6500.00 | 3 |
| 9 | 50 | 7500.00 | 1 |
| 8 | 50 | 6500.00 | 2 |
+ ------- + -------- + ---------- + ------ +

3 SQL to achieve

4 results demonstrates

+ ------- + -------- + ---------- + ------ +
| Empid | deptid | salary | rank |
+ ------- + -------- + ---------- + ------ +
| 1 | 10 | 5500.00 | 1 |
| 2 | 10 | 4500.00 | 2 |
| 4 | 20 | 4800.00 | 1 |
| 3 | 20 | 1900.00 | 2 |
| 7 | 40 | 44500.00 | 1 |
| 6 | 40 | 14500.00 | 2 |
| 5 | 40 | 6500.00 | 3 |
| 9 | 50 | 7500.00 | 1 |
| 8 | 50 | 6500.00 | 2 |
+ ------- + -------- + ---------- + ------ +

9 rows in set (0.00 sec)

(5) Summary

This SQL is the use of the MYSQL flexible and user variables call through this case, we can learn by analogy to write more wonderful SQL.
Original Source: user variable called hope we can write more exciting SQL giving top priority to this case.
原文http://www.databasesql.info/article/7960256392

Posted in: MySQL practise