用户的MYSQL SELECT等级(大于x和小于y) [英] MYSQL SELECT rank of user (more than x & less than y)

问题描述

我有点受php/Mysql查询的困扰.我有2张桌子:

I'm a bit stuck with a php/Mysql Query. I got 2 tables :

  table_users              table_ranks
----------------    ------------------------
| id  | points |    | name | points_needed |
----------------    ------------------------
| 1   |   2    |    | lvl0 |      0        |
| 2   |   10   |    | lvl1 |      10       |
| 3   |   21   |    | lvl2 |      20       |
| 4   |   29   |    | lvl3 |      30       |
----------------    ------------------------

我需要这样的输出:

• 用户_1 = lvl0(因为用户有2分)
• User_2 = lvl1(因为用户刚达到10分)
• ...
• User_4 = lvl2(因为用户尚未达到30分)

想你了:)

致谢.

推荐答案

您可以这样做

SELECT
  tu.id,
  tr.name,
  tu.points
FROM table_ranks as tr
  LEFT JOIN (SELECT * FROM table_ranks LIMIT 1,69596585953484) as l
    ON l.points_needed = (SELECT MIN(points_needed) FROM table_ranks WHERE points_needed > tr.points_needed limit 1)
  LEFT OUTER JOIN table_users AS tu ON tu.points >= tr.points_needed AND tu.points < l.points_needed
WHERE tu.id IS NOT NULL
group by tu.id

小提琴

输出

-------------------------
| id  | points   | name |
-------------------------
| 1   |   lvl0   | 2    |
| 2   |   lvl1   | 10   |
| 3   |   lvl2   | 21   |
| 4   |   lvl2   | 29   |
-------------------------

这篇关于用户的MYSQL SELECT等级(大于x和小于y)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！