在主流的数据库中, 一般都是有row_number函数直接支持分组排序的, 但是MySQL没有.
但我们可以通过以下方式实现:
准备工作
- 安装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) 和客户端不能链接
创建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);
实现代码如下
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;
结果
+-------+--------+----------+------+
| 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)
如有问题, 欢迎评论留言!
