mysql实现分组排序[转]

Nov 21, 2016 | | Say something

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">