mysql - 找到用户的排名位置

Question

我有这些表

用户

+----------+---------------+
| id_users |   usr_email   |
+----------+---------------+
| 1        | a@domain.com  |
| 2        | b@domain.com  |
| 3        | c@domain.com  |
| 4        | d@domain.com  |
| 5        | e@domain.com  |
+----------+---------------+

排名

+-------------+-----------+----------+
| id_ranking  | id_users  |  points  |
+-------------+-----------+----------+
| 50          | 1         | 27       | //3rd
| 51          | 2         | 55       | //1st
| 52          | 3         | 9        | //5th
| 53          | 4         | 14       | //4th
| 54          | 5         | 38       | //2nd
+-------------+-----------+----------+

我想通过电子邮件过滤来撤销用户的数据及其排名位置。例如，如果我想要邮件 c@domain.com 的信息，我应该得到

+----------+--------|---------------+
| id_users | points | rank_position |
+----------+--------|---------------+
| 3        | 9      | 5             |
+----------+--------|---------------+

我找到了这段返回排名位置的查询

SELECT x.id_users, x.position
FROM (
    SELECT t1.id_ranking, t1.id_users, @rownum := @rownum + 1 AS position
    FROM ranking t1
    JOIN (SELECT @rownum := 0) r ORDER BY t1.points desc
) x
WHERE x.id_users = 3

但我无法在旧查询中使用它

select u.*, r.points
from users u 
left join ranking r on r.id_users = u.id_users
where u.usr_email = 'c@domain.com'

我的尝试

select u.*, r.points, p.*
from users u 
left join ranking r on r.id_users = u.id_users,

(SELECT x.id_users, x.position
FROM (
    SELECT t1.id_ranking, t1.id_users, @rownum := @rownum + 1 AS position
    FROM ranking t1
    JOIN (SELECT @rownum := 0) r ORDER BY t1.points desc
) x
 WHERE x.id_users = u.id_users) p

where u.usr_email = 'c@domain.com'

有什么帮助吗？

Answer 1

您缺少加入条件。而且，ranking 的外部连接并不是必需的。您可以“记住”子查询中的点:

select u.*, r.points, r.position
from users u left join
     (select r.*, @rownum := @rownum + 1 AS position
      from ranking r CROSS JOIN
           (SELECT @rownum := 0) r
      order by r.points desc
     ) r
     ON r.id_users = u.id_users
where u.usr_email = 'c@domain.com'