MYSQL-實現ORACLE- row_number() over(partition by ) 分組排序功能

標籤：des   style   blog   http   io   color   ar   sp   資料   

MYSQL-實現ORACLE- row_number() over(partition by ) 分組排序功能

 

　　由於MYSQL沒有提供類似ORACLE中OVER()這樣豐富的分析函數. 所以在MYSQL裡需要實現這樣的功能,我們只能用一些靈活的辦法:

1.首先我們來建立執行個體資料:

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. 確定需求: 根據部門來分組,顯示各員工在部門裡按薪水排名名次.

顯示結果預期如下:

+-------+--------+----------+------+| 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)

 

3. SQL 實現

SELECT    empid,    deptid,    salary,    rankFROM    (        SELECT            empid,            deptid,            salary,        IF (            @pdept = src.deptid ,@rank := @rank + 1 ,@rank := 1        ) AS rank,        @pdept := src.deptid AS g    FROM        (            SELECT                empid,                deptid,                salary            FROM                heyf_t10            ORDER BY                deptid ASC,                salary DESC        ) src,        (            SELECT                @pdept := NULL ,@rank := 0        ) var    ) z;

 

4. 結果示範

mysql> SELECT    ->  empid,    ->  deptid,    ->  salary,    ->  rank    -> FROM    ->  (    ->          SELECT    ->                  empid,    ->                  deptid,    ->                  salary,    ->    ->          IF (    ->                  @pdept = src.deptid ,@rank := @rank + 1 ,@rank := 1    ->          ) AS rank,    ->          @pdept := src.deptid AS g    ->  FROM    ->          (    ->                  SELECT    ->                          empid,    ->                          deptid,    ->                          salary    ->                  FROM    ->                          heyf_t10    ->                  ORDER BY    ->                          deptid ASC,    ->                          salary DESC    ->          ) src,    ->          (    ->                  SELECT    ->                          @pdept := NULL ,@rank := 0    ->          ) var    ->  ) z;+-------+--------+----------+------+| 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)

 

 

 

 

轉自:　　http://ace105.blog.51cto.com/639741/724411

 

MYSQL-實現ORACLE- row_number() over(partition by ) 分組排序功能