用最少的Sql实现一个特殊排行榜

引言

 说起排行榜上一篇已经介绍过了用Redis实现的方法，而这篇完全是用Sql来实现，不使用任何缓存。

需求功能（1）

 现有十个同学，年龄分布如下图所示，假设你是戴七，请根据年龄获得你在这十人中排第几？同时请获得你的前一名和后一名各是哪一位同学？

 这个题是我去年在一家公司被问到的面试题。根据上图分析，获取到自己的排名并不是难事，那前一名和后一名也非常简单了。

自己的排名

// 原生写法
select count(*) from `rank` where `age` > 22 or (`age` = 22 and `created_at` > '2020-01-21 09:05:34');

// Laravel框架写法
$my = DB::table('rank')->where('name', '戴七')->first();
$rank = DB::table('rank')->where('age', '>', $my->age)->orWhere(function ($query) use ($my) {
    $query->where('age', '=', $my->age)->where('created_at', '>', $my->created_at);
})->count();
$myRank = $rank + 1;

 这里要注意的是：如果两个人的年龄相同，那么就要比较时间。最终得到的是年龄大于自己人有多少个，+1就是自己的排名。

前一名

// 通过上面的Sql得知年龄大于自己的人数是6人
$rankNum = 6;
$top = $rankNum - 1;
select * from rank where age > 22 order by age desc limit $top,1

后一名

select * from `rank` where `age` < 22 or (`age` = 22 and `created_at` < '2020-01-21 09:05:34') order by age desc,created_at desc limit 1

 这里和原始Sql不同的是：年龄是<22，时间也是<自己的出生时间。

需求功能（2）

 还是这十名同学，假设戴七同学就是本人，现需要五名同学的年龄作为参考，要求这分别得知你本人的前两名同学和后两名同学都是谁（五名同学中必须包含你自己）？

特殊情况：

 （1）如果你是第一名，那么就获得你后面的四位同学。
 （2）如果你是第二名，那么就获得你前面的一名+后面的三名同学。
 （3）如果你是倒数第一名，那么就获得你前面的四位同学。
 （4）如果你是倒数第二名，那么就获得你前面的三名+后面的一位同学。
 （5）可使用其他语言辅助解决问题，但尽可能的简单清晰。

答案

$myRank = $rank + 1;
$total = Db::table('rank')->count();
// 如果排名是在前三名
if ($myRank <= 3) {
    select * from rank where 1 order by age desc,created_at desc limit 5;
} else if (($total - $myRank) <= 2) { // 倒数两名
    $unRank = $total - $myRank + 1;
    $offset = $myRank - 6 + $unRank;
    select * from rank where 1 order by age desc,created_at desc limit $offset,5;
} else {
    $offset = $myRank - 3;
    select * from rank where 1 order by age desc,created_at desc limit $offset,5;
}