MySQL和Mariadb中的分组排序区别

在MySQL和MariaDB中都可以实现分组排序，但实现上仍存在稍微差别。测试环境分别选择 MySQL社区版MySQL8.0.21.0和MariaDB10.3.12。

下面通过实例进行测试：

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

通过部门编号分组后，在一个部门内对字段 salary 进行降序排列：

1.在MaraiDB中实现时，需要在内层子查询中使用limit关键字才能完成分组排序的预期效果

SELECT
 deptid, salary, rk
FROM (
 SELECT
 tmp.deptid, tmp.salary, @rownum:=@rownum+1,
 case when @pdept=tmp.deptid then @rank:=@rank+1 else @rank:=1 end AS rk,
 @pdept:=tmp.deptid
 FROM (
 SELECT deptid, salary FROM row_number_test
 ORDER BY deptid ASC, salary DESC limit 1000000
 ) tmp, (SELECT @rownum:=0, @pdept:=null, @rank:=0) a
) result;

如果不添加limit关键字的查询效果如下:

2.在MySQL8.0中不需要在查询中加limit 就可以完成分组排序的效果。

SELECT
 deptid, salary, rk
FROM (
 SELECT
 tmp.deptid, tmp.salary, @rownum:=@rownum+1,
 case when @pdept=tmp.deptid then @rank:=@rank+1 else @rank:=1 end AS rk,
 @pdept:=tmp.deptid
 FROM (
 SELECT deptid, salary FROM row_number_test
 ORDER BY deptid ASC, salary DESC limit 1000000
 ) tmp, (SELECT @rownum:=0, @pdept:=null, @rank:=0) a
) result;