mysql8.0之前 是没有rank函数的,那我们要实现排名功能的方法可以是这样的
SELECT
sid, sname, sage,
@curRank := @curRank + 1 AS rank1
FROM student s,
(SELECT @curRank := 0) r
ORDER BY sage
mysql8.0提供了rank函数
RANK()
函数为结果集的分区中的每一行分配一个排名。行的等级由一加上前面的等级数指定
RANK() OVER ( PARTITION BY <expression>[{,<expression>...}] ORDER BY <expression> [ASC|DESC], [{,<expression>...}] )
partition by 作为分区,可以不进行分区。
SELECT
sid,
sname,
sage,
rank ( ) over ( ORDER BY sage )
FROM
student s
ORDER BY
sage
加入分区的效果
SELECT
sid,
sname,
sage,
rank ( ) over (PARTITION by Ssex ORDER BY sage )
FROM
student s
ORDER BY
ssex,sage
另外:rank函数的性能还是很好的