mysql不同版本实现排名Rank

该文章是转载，主要是因为自己遇到了这个问题，想做个记录，对于mysql不同版本如何实现排名。

文章原链接：https://www.cnblogs.com/brxHqs/p/11124558.html

--------每个学院每条记录的刷卡时间排名（千万级）------------------

5.7：

SELECT t.academy,@curRank := @curRank + 1 AS rank
FROM (select * from `smartlib_base`.z_gctrl_ctrl_sys tt WHERE tt.enter_time >= '2014-01-01 08:52:26')t, (
SELECT @curRank := 0
) q
ORDER BY t.enter_time;

8.0：
select t.academy_code, rank() over(partition by t.academy_code order by t.operation_time)as rk FROM
(select * from `smartlib_base_2.0.19`.b_gate tt WHERE tt.operation_time >= '2014-01-01 08:52:26')t;

-----------各个学院的刷卡总数排名（百级数据）------------------

5.7：
SELECT t.academy,t.SCORE,@curRank := @curRank + 1 AS rank
FROM ( select count(enter_time) AS SCORE,
academy from z_gctrl_ctrl_sys
GROUP BY academy HAVING
'enter_time' >= '2014-01-01 08:52:26' )t, (
SELECT @curRank := 0
) q
ORDER BY t.SCORE DESC;

8.0：

select t.academy_code, t.`总数`, rank() over( order by t.`总数`)as rk FROM
(select count(operation_time)as `总数`,academy_code from b_gate
GROUP BY academy_code HAVING
'operation_time' >= '2014-01-01 08:52:26' )t;

mysql8.0提供的函数，速度比5.7快一倍