php - 如何创建等级系统

Question

我有一个用户表， View 侧有 user_score 列，我想显示每个用户的排名和分数以及他之前/之后的 2 个人。

如何使用 MySQL 或 PHP 以最短的执行时间实现此目的？

示例

+-----------+------------+
| User_id   | Score      |
+-----------+------------+
| 1         | 12258      |
| 2         | 112        |
| 3         | 9678       |
| 4         | 689206     |
| 5         | 1868       |
+-----------+------------+

例如我的user_id = 3。我想这样显示

                    you
                   9678    

 user befor you               user after you
    12258                            1868       

Answer 1

如果您只想使用 MySQL:

SELECT usr.user_id, (SELECT usr1.user_score FROM user usr1 WHERE usr.user_score >= usr1.user_score AND usr.user_id != usr1.user_id ORDER BY usr1.user_score DESC LIMIT 1) AS user_before_you, usr.user_score AS you, (SELECT usr1.user_score FROM user usr1 WHERE usr.user_score <= usr1.user_score AND usr.user_id != usr1.user_id ORDER BY usr1 .user_score ASC LIMIT 1) AS user_after_you FROM user usr WHERE 1