最简单的“返回高分"使用用户ID的SQL查询 [英] most simple "return highscores" SQL Query using userID

问题描述

高处

在Android上最小的游戏上工作，我在涉及SQL查询时迷失了方向.

working at a minimal game on Android I get lost when it comes to sql queries.

我正在尝试获得排名前100位的列表，它几乎可以正常运行，但效果不佳

I'm trying to get a top 100 ranking list and it almost works but not quite

我正在使用此查询

$sql = "SELECT * FROM WorldGames_table GROUP BY user_id ORDER BY score DESC LIMIT 100";

问题在于，它以正确的顺序返回了前100个得分的列表，并且每个用户仅包含1个得分.但是显示的并不总是用户的最佳分数.

the thing is that it returns a list of the top 100 scores, in the proper order and containing only 1 score of each user. But it is not always the best score of the users that is shown.

我对复杂查询的组织"方式感到完全困惑，这就是为什么我要寻找简单的查询

I am getting completely confused by the way complexes queries are "organised", that is why I am looking for a simple query

欢呼

更新:已解决(或者我当时虽然是:s)

UPDATE: Solved (or so I though at the time :s)

这是返回我正在寻找的内容的行:D

this is the line that returns what i was looking for :D

SELECT WorldGames_table .*, MAX(score) as score FROM WorldGames_table GROUP BY id, device_id ORDER BY score DESC LIMIT 100;

感谢AT-2016和每一个伸出援助之手的人:D

Thanks to AT-2016 and to every one who gave a hand :D

UPDATE II :毕竟没有解决:S

UPDATE II: not solved after all :S

很高兴能在排名中显示正确的高分，但我没有意识到，实际上，通过此查询每个用户的所有分数都在显示，而不是只返回每个用户的最佳分数. 在这里，我再次使用userID搜索最简单的返回高分" SQL查询

So happy that the proper Highscores were showing up in the ranking, I didn't realise that, actually, with this query All the scores from each user are showing up, instead of returning only the best score of each user. Here I go again, in the search of the most simple "return highscores" SQL Query using userID

更新III :我认为答案可能来自

UPDATE III: I think the answer might come from this place so I put this together, and again, it works in my local test and fails when a apply it on my server

SELECT a.id, a.timestamp, a.name, a.score, a.color, a.flower, a.user_id

FROM games_table a

INNER JOIN (

    SELECT id, MAX(score) score
    FROM games_table
    GROUP BY user_id

) b ON a.id = b.id AND a.score = b.score

ORDER BY a.score DESC

LIMIT 100 

仍在调查piñata方式;)

Still investigating, the piñata way ;)

UPDATE IV :我想我有(但是我不会在未完成之前完成) 同时，这就是我所发现的.没有，另一个失败的尝试:(

UPDATE IV: I think I have it (but I won't it is done before I'm sur) In the mean time this is what I found. NOPE, ANOTHER FAILED ATTEMPT :(

我直接在PHPmyAdmin中尝试了查询

I tried my queries directly inside PHPmyAdmin

发现该查询

SELECT
timestamp, name, MAX(score), color, flower, device_id 

FROM
 WorldFlowers_table 

GROUP BY 
 device_id 

ORDER BY
 score 
 DESC 

LIMIT 100;

返回了这样的错误消息: 当前选择不包含唯一列.网格编辑，复选框，编辑，复制和删除功能不可用."

returned an error message like this: "Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available."

所以我在第2行的开头添加了"id"

So I added "id," at the beginning of line #2

现在看来我得到了我想要的结果 我做了一些测试，但我会确保将问题标记为已回答"

and now it looks like I get the result I was looking for I did some test but I will make sur it works to mark the question as "answered"

没有用，现在我正在查看

Did not work, now I'm looking at this thread without much hope

我今天获得的唯一一件事是，我直接在phpmyadmin中测试了查询

The only thing I gained today is that I test the queries directly into phpmyadmin

如果外面有人帮助！

更新V :一个名为scaisEdge的人给了我这个答案，至少到目前为止，它似乎仍然有效.等待更多测试说已回答"

UPDATE V: someone called scaisEdge gave me this answer and, at least until now, it seems to work. Waiting for more testing to say "answered"

  select * from WorldFlowers_table
  where  (device_id, score) in (  select device_id, max(score) 
                                  FROM WorldFlowers_table 
                                  GROUP BY device_id  
                                  ORDER BY score DESC 
                                  )
  ORDER BY score DESC 
  LIMIT 100 

UPDATE VI

它有效！！ :)

就像我上面说的那样，答案是由scaisEdge给我的，这是我在此处发布的这个问题的另一个实例" mySQL获得前100名得分"查询让我发疯了

and the answer was given to me , like I said above, by scaisEdge, in another "instance" of this question that I posted here mySQL "get TOP 100 scores" query is turning me crazy

推荐答案

尝试使用MAX()获得最高值:

Try to use MAX() to get the highest values:

SELECT MAX(Values) FROM WorldGames_table 
GROUP BY user_id ORDER BY score DESC LIMIT 100

请尝试以下操作:

SELECT *
FROM Table JOIN
    (SELECT MIN(Score) as Values -- Or use MAX()
      FROM (SELECT Score
            FROM Table
            ORDER BY Score
            LIMIT 100
           ) Table2
    ) Table2
ON Table.Score >= Table2;

这篇关于最简单的“返回高分"使用用户ID的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！