在主流的數(shù)據(jù)庫中, 一般都是有row_number函數(shù)直接支持分組排序的, 但是MySQL沒有.
但我們可以通過以下方式實現(xiàn):

準(zhǔn)備工作

1. 安裝MySQL

brew install mysql
mysql.server start 

筆主MySQL版本

mysql  Ver 8.0.13 for osx10.14 on x86_64 (Homebrew)

若遇到問題, 請點擊打開: MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 和客戶端不能鏈接

創(chuàng)建row_number_test測試表

DROP TABLE IF EXISTS row_number_test;  
CREATE TABLE row_number_test(empid INT, deptid INT, salary DECIMAL(10, 2)); 
INSERT INTO row_number_test 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); 

實現(xiàn)代碼如下

SELECT 
    empid, deptid, salary, rk 
FROM ( 
    SELECT 
        tmp.empid, tmp.deptid, tmp.salary, @rownum:=@rownum+1, 
        IF(@pdept=tmp.deptid, @rank:=@rank+1, @rank:=1) AS rk, 
        @pdept:=tmp.deptid
    FROM (
        SELECT empid, deptid, salary FROM row_number_test 
        ORDER BY deptid ASC, salary DESC
    ) tmp, (SELECT @rownum:=0, @pdept:=null, @rank:=0) a
) result; 

結(jié)果

+-------+--------+----------+------+
| empid | deptid | salary   | rk   |
+-------+--------+----------+------+
|     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, 7 warnings (0.01 sec)

如有問題, 歡迎評論留言!