可能的重复项:
SQL 按"分组依据"内的总计排序
更新:我已经找到了我的解决方案,我在这里发布了。感谢大家的帮助!
我正在开发一个需要排行榜的Facebook应用程序。记录完成游戏所需的分数和时间,并首先按分数组织,然后在两个相同分数的情况下使用时间。如果用户玩了多次,则使用他们的最佳分数。
分数越低,游戏中的表现越好。
我的表结构是:
id
facebook_id - (Unique Identifier for the user)
name
email
score
time - (time to complete game in seconds)
timestamp - (unix timestamp of entry)
date - (readable format of timestamp)
ip
我认为可行的查询是:
SELECT *
FROM entries
ORDER BY score ASC, time ASC
GROUP BY facebook_id
我遇到的问题是在某些情况下,它会在数据库中拉入用户的第一个分数,而不是他们的最高分数。我认为这取决于 GROUP BY 语句。我本以为 ORDER BY 语句会解决这个问题,但显然不是。
例如:
----------------------------------------------------------------------------
| ID | NAME | SCORE | TIME | TIMESTAMP | DATE | IP |
----------------------------------------------------------------------------
| 1 | Joe Bloggs | 65 | 300 | 1234567890 | XXX | XXX |
----------------------------------------------------------------------------
| 2 | Jane Doe | 72 | 280 | 1234567890 | XXX | XXX |
----------------------------------------------------------------------------
| 3 | Joe Bloggs | 55 | 285 | 1234567890 | XXX | XXX |
----------------------------------------------------------------------------
| 4 | Jane Doe | 78 | 320 | 1234567890 | XXX | XXX |
----------------------------------------------------------------------------
当我使用上面的查询时,我得到以下结果:
1. Joe Bloggs - 65 - 300 - (Joes First Entry, not his best entry)
2. Jane Doe - 72 - 280
我本来以为...
1. Joe Bloggs - 55 - 285 - (Joe's best entry)
2. Jane Doe - 72 - 280
这就像 Group By 忽略了顺序 - 只是覆盖了值。
将 MIN(score) 与分组依据一起使用会选择最低分数,这是正确的 - 但是它合并了数据库中用户第一条记录的时间,因此经常返回错误。
那么,如何选择用户的最高分数以及相关的时间、名称等,并按分数和时间对结果进行排序?
提前感谢!
您的查询实际上没有意义,因为排序依据应该在分组依据之后。 您使用的是哪个 SQL 引擎? 大多数人会给出错误。
我认为你想要的更像是:
select e.facebookid, minscore, min(e.time) as mintime -- or do you want maxtime?
from entries e join
(select e.facebookid, min(score) as minscore
from entries e
group by facebookid
) esum
on e.facebookid = esum.facebookid and
e.score = e.minscore
group by e.facebookid, minscore
您也可以使用窗口函数执行此操作,但这取决于您的数据库。
一种方法是这样的:
SELECT entries.facebook_id, MIN(entries.score) AS score, MIN(entries.time) AS time
FROM entries
INNER JOIN (
SELECT facebook_id, MIN(score) AS score
FROM entries
GROUP BY facebook_id) highscores
ON highscores.facebook_id = entries.facebook_id
AND entries.score = highscores.score
GROUP BY entries.facebook_id
ORDER BY MIN(entries.score) ASC, MIN(entries.time) ASC
如果需要条目表中的更多信息,则可以将其用作子查询,并在显示的信息(facebook_id、分数、时间)上再次联接,以便为每个用户获取一行。
你需要聚合两次,是这个的关键;一次是找到用户的最低分数,再次找到该用户和分数的最小时间。您可以颠倒聚合的顺序,但我希望这将最快地过滤,从而最有效。
您可能还想检查哪个更快,第二次聚合:使用最低分数或同时使用分数分组。
你需要减分
SELECT
facebook_id,
name,
email,
min(score) as high_score
FROM
entries
GROUP BY
facebook_id,
name,
email
ORDER BY
min(score) ASC
感谢您的帮助。 @Penguat有最接近的答案。这是我对可能遇到相同问题的任何人的最后一个查询......
SELECT f.facebook_id, f.name, f.score, f.time FROM
(SELECT facebook_id, name, min(score)
AS highscore FROM golf_entries
WHERE time > 0
GROUP BY facebook_id)
AS x
INNER JOIN golf_entries as f
ON f.facebook_id = x.facebook_id
AND f.score = x.highscore
ORDER BY score ASC, time ASC
再次感谢!
如果你想要他们最好的时间,你想使用 MIN() 函数 - 你说分数越低,他们做得越好。
SELECT facebook_id, MIN(score), time, name, ...
FROM entries
GROUP BY facebook_id, time, name, ...
ORDER BY score, time