Я немного застрял в запросе php / Mysql. У меня есть 2 стола:
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 | ---------------- ------------------------ Мне нужен такой вывод:
Думаю, вы 🙂
С уважением.
Вы можете сделать это так
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 | -------------------------
дайте это попробовать, немного беспорядочно из-за дизайна стола,
SELECT u.*, r.name FROM table_users u INNER JOIN ( SELECT x.name, x.points_needed start_point, COALESCE(y.points_needed - 1, 2000000) end_point FROM ( SELECT name, points_needed, @rank:=@rank+1 ranks FROM table_ranks a, (SELECT @rank:=0) b ORDER BY points_needed ) x LEFT JOIN ( SELECT name, points_needed, @rank1:=@rank1+1 ranks FROM table_ranks a, (SELECT @rank1:=0) b ORDER BY points_needed ) y ON x.ranks+1 = y.ranks ) r ON u.points BETWEEN r.start_point AND r.end_point