MySQL使用SQL实现Oracle Rank函数功能

需求：按照用户的积分对用户进行排名。

事例表结构

CREATE TABLE `users_points` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `score` int(11) NOT NULL COMMENT '分数',
  `score_rank` int(11) NOT NULL COMMENT '分数排名',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

依据score字段，对user_id进行排名，MySQL可以使用一条SQL实现

select a.user_id, a.score, (select count(distinct b.score) from users_points b where b.score >= a.score) as rank from users_points a order by score desc;

得出的结果

+----+-------+------+
| user_id | score | rank |
+----+-------+------+
| 11 |   100 |    1 |
|  8 |    80 |    2 |
|  4 |    50 |    3 |
| 12 |    45 |    4 |
|  2 |    40 |    5 |
|  6 |    30 |    6 |
|  1 |    20 |    7 |
| 10 |    15 |    8 |
|  7 |    10 |    9 |
|  5 |    10 |    9 |
|  3 |    10 |    9 |
|  9 |     5 |   10 |
+----+-------+------+