在mysql表中选择不同的值

Here's my mysql table:

这是我的mysql表:

Table League (userid and lid are primary keys):

表联盟(用户ID和盖子是主键):

*userid*  *lid*     rank    win     loss     streak  score
---------------------------------------------------------
    2       1        1       2       0         2      10
    2       3        2       1       1         1       5
    5       1        2       1       1         1       5

I'm trying to select the users with the top score only once. For example since userid 2 is in league (lid) 1 and 3, only his top score will be selected in the query. So in that case the row with score 10 would be selected since that's the users top score from both league 1 and 3. The row with lid 3 will not be selected.

我试图只选择一次得分最高的用户。例如,由于用户ID 2在联盟(盖子)1和3中,因此在查询中仅选择他的最高分。因此,在这种情况下,将选择具有得分10的行,因为这是来自联赛1和3的用户最高得分。将不选择具有盖子3的行。

So the query results should look like this:

所以查询结果应如下所示:

userid  lid     rank    win     loss     streak  score
---------------------------------------------------------
  2      1       1       2       0         2      10
  5      1       2       1       1         1       5

As you can see userid 2 with lid 3 was not in the result because the score 10 from lid 1 was grater than score 5 from league 3. Any ideas?

你可以看到带有盖子3的用户2不在结果中,因为来自盖子1的得分10比来自联赛3的得分5更高。任何想法?

3 个解决方案

#1

SELECT l.userid, u.username, l.lid, l.rank, l.win, l.loss, l.streak, l.score
    FROM (SELECT userid, MAX(score) AS MaxScore
              FROM League 
              GROUP BY userid) q
        INNER JOIN League l
            ON q.userid = l.userid
                AND q.MaxScore = l.score
        INNER JOIN users u
            ON l.userid = u.userid

#2

SELECT *
FROM table t1
WHERE t1.score = (SELECT MAX(t2.score)
  FROM table t2
  WHERE t1.userid = t2.userid)

This will display ties, don't know if you want those or not.

这将显示关系,不知道你是否想要那些。

#3

Here the simplest solution:

这是最简单的解决方案:

SELECT *
FROM League t1 
WHERE 
  t1.lig = (SELECT t2.lig
        FROM League t2
        WHERE t2.userid = t1.userid
        ORDER BY score desc
        LIMIT 1
     )

#1

SELECT l.userid, u.username, l.lid, l.rank, l.win, l.loss, l.streak, l.score
    FROM (SELECT userid, MAX(score) AS MaxScore
              FROM League 
              GROUP BY userid) q
        INNER JOIN League l
            ON q.userid = l.userid
                AND q.MaxScore = l.score
        INNER JOIN users u
            ON l.userid = u.userid

#2

SELECT *
FROM table t1
WHERE t1.score = (SELECT MAX(t2.score)
  FROM table t2
  WHERE t1.userid = t2.userid)