MySQL-在JOINS中查找记录的位置 [英] MySQL - Finding the position of a record in JOINS

问题描述

我有三个表，分别是包含用户信息的registrations，包含用户ID和他们当前所在级别的leaderboard，最后是"clear_times"，它记录了用户清除特定级别的时间.

I have three tables namely registrations containing users' information, leaderboard containing users' ID and the level they are currently on and finally 'clear_times" which records the time when users clear a certain level.

我正在使用MySQL生成排行榜.最高级别的用户将位于顶部.同一级别的用户将根据清除级别的时间clear_time进行排名. (首先要清除的级别是排名靠前的.)

I'm generating a leaderboard using MySQL. User in the highest level will stand on the top. Users in same level will be ranked according to clear_time, the time when they have cleared the level. (First to clear the level is ranked ahead).

以下是我用于此目的的代码:

Following is the code I'm using for the purpose:

SELECT t1.level, t3.user_id, t3.user_id2, t3.user_type
FROM leaderboard AS t1
INNER JOIN clear_times AS t2 ON ( t1.uid = t2.uid AND (t1.level -1) = t2.level ) 
INNER JOIN registrations AS t3 ON ( t1.uid = t3.id ) 
ORDER BY t1.level DESC , t2.clear_time ASC

id' is a primary key of and uid`是外键.

该代码非常适合生成排行榜.现在，我想根据给定用户id来查找特定用户的排名.我可以在服务器端脚本编写，遍历记录和使用计数器变量的情况下做到这一点.但是我相信MySQL本身有一种方法可以以某种方式操纵上面的代码，而我做不到.

The code works perfect for generating leaderboard. Now, I want to find out the rank of a particular user given his/her id. I can this can be done on server-side scripting, looping through the records and using a counter variable. But I believe there is a way of doing this in MySQL itself manipulating in someway the above code, which I couldn't do.

推荐答案

一旦您知道目标用户的level和clear_time，您只需要计算排名高于给定顺序的记录数即可. :

Once you know the target user's level and clear_time, you merely need to count the number of records that are ranked higher than that in the given ordering:

SELECT COUNT(*)
FROM   leaderboard   AS t1
  JOIN clear_times   AS t2 ON t1.uid = t2.uid AND t1.level = t2.level + 1
  JOIN registrations AS t3 ON t1.uid = t3.id
WHERE  t1.level > ? OR (t1.level = ? AND t2.clear_time < ?)

如果需要，可以在同一查询中使用其他联接或子查询来获取有关目标用户的丢失信息.我会将其留给读者练习.

You can use further joins or subqueries to obtain the missing information about the target user within the same query, if so desired. I will leave that as an exercise for the reader.

这篇关于MySQL-在JOINS中查找记录的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！