mysql用于分组排序_mysql实现分组排序[转]

11月 21, 2016 |

Nix.Huang

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