MySQL实现Oracle的row_number()over(partition by)

在项目开发中遇到数据库从Oracle换成MySQL，遇见很多sql语法错误；

在网上找到解决问题并摘录下来希望以后碰见这些问题的朋友们可以看到以方便解决自己的问题

（***本文转自http://www.databasesql.info/article/7960256392）

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:
drop table if exists heyf_t10;
create table heyf_t10 (empid int, deptid int, salary decimal (10,2));
Insert into heyf_t10, values
(1,10,5500.00)
(2,10,4500.00)
(3,20,1900.00)
(4,20,4800.00)
(5,40,6500.00)
(6,40,14500.00)
(7,40,44500.00)
(8,50,6500.00)
(9,50,7500.00);

(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
select empid, deptid, salary, rank from (
select heyf_tmp.empid, heyf_tmp.deptid, heyf_tmp.salary, @ rownum: = @ rownum +1,
if (@ pdept = heyf_tmp.deptid, @ rank: = @ rank +1, @ rank: = 1) as rank,
@ Pdept: = heyf_tmp.deptid
from (
select empid, deptid, salary from heyf_t10 order by deptid asc, salary desc
) Heyf_tmp, (select @ rownum: = 0, @ pdept: = null, @ rank: = 0) a) result
;

4 results demonstrates

mysql> select empid, deptid, salary, rank from (
-> Select heyf_tmp.empid, heyf_tmp.deptid, heyf_tmp.salary, @ rownum: = @ rownum +1,
-> If (@ pdept = heyf_tmp.deptid, @ rank: = @ rank +1, @ rank: = 1) as rank,
-> @ Pdept: = heyf_tmp.deptid
-> From (
-> Select empid, deptid, salary from heyf_t10 order by deptid asc, salary desc
->) Heyf_tmp, (select @ rownum: = 0, @ pdept: = null, @ rank: = 0) a) result
->;
+ ------- + -------- + ---------- + ------ +
| 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