我有一个名为 Scores 的表,它包含以下列: id , player_id , value1 , value2 , value3 和 date 。
该表格包含以下内容:
+ ------ + ----------- + -------- + -------- + -------- + ------------ + | id | player_id | value1 | value2 | value3 |日期| + ------ + ----------- + -------- + -------- + -------- + ------------ + | 1 | 1 | 10 | 0 | 0 | 2012-08-02 | + ------ + ----------- + -------- + -------- + -------- + ------------ + | 2 | 2 | 15 | 1 | 0 | 2012-08-03 | + ------ + ----------- + -------- + -------- + -------- + ------------ + | 3 | 3 | 9 | 0 | 0 | | 2012-08-04 | + ------ + ----------- + -------- + -------- + -------- + ------------ + | 4 | 1 | 11 | 0 | 0 | | + ------ + ----------- + -------- + -------- + -------- + ------------ + | 5 | 2 | 16 | 2 | 0 | 2012-08-06 | + ------ + ----------- + -------- + -------- + -------- + ------------ + | 6 | 2 | 15 | 0 | 0 | 2012-08-07 | + ------ + ----------- + -------- + -------- + -------- + ------------ +我想要查询它会返回按照value1,value2,value3中的值排序的每个玩家的最佳高分。值1是比较重要的字段,值2中等重要,值3不太重要,例如:
value1 = 15 value1 = 15 value2 = 1大于 - > value2 = 0 value3 = 0 value3 = 1需要的是:
+ ------ + ----------- + --- ----- + -------- + -------- + ------------ + | id | player_id | value1 | value2 | value3 |日期| + ------ + ----------- + -------- + -------- + -------- + ------------ + | 5 | 2 | 16 | 2 | 0 | 2012-08-06 | + ------ + ----------- + -------- + -------- + -------- + ------------ + | 4 | 1 | 11 | 0 | 0 | | + ------ + ----------- + -------- + -------- + -------- + ------------ + | 3 | 3 | 9 | 0 | 0 | | 2012-08-04 | + ------ + ----------- + -------- + -------- + -------- + ------------ +我试着用 MAX , DISTINCT , GROUP BY 和子查询,但我没有得到正确的结果。基本上它是下一个查询,但选择每个组的第一行:
SELECT id,player_id,value1,value2, value3 FROM scores ORDER BY value1 DESC,value2 DESC,value3 DESC------ 编辑1 ------- 的答案效果不错,也许,表现不太好。我需要将他的解决方案与大型数据库进行比较,以检查响应时间。
我有一个想法(和可能的解决方案)。解决方案包括添加新的布尔列,表示该分数是否为该玩家的最佳分数。这样,我需要检查新的分数是否比该分析器的最佳旧分数更好,当我将新分数添加到数据库时,如果是这样,我需要在旧的最佳分数中将该标记标记为假,新的分数。这给了我一种直接检索每个球员最好成绩的方法(简单的查询,例如 SELECT ... FROM .... ORDER BY )。
------ 编辑2 -------
weicap's回答是最快的解决方案。我不知道为什么,但是他的查询比 eggyal的查询快两倍。 $ b ------ 编辑3 ------- 我错了,如果查询先前被缓存,weicap的查询速度会更快,如果查询不是十秒或更长时间的话。在更改中,weicap的答案总是需要300-400毫秒,而不是80.000行。
解决方案b
SELECT player_id,(SELECT value1 FROM得分b其中a.player_id = b.player_id ORDER BY value1 DESC,value2 DESC, value3 DESC limit 1)作为value1,(SELECT value2 FROM得分b,其中a.player_id = b.player_id ORDER BY value1 DESC,value2 DESC,value3 DESC limit 1)as value2, (SELECT value3 FROM得分b其中a.player_id = b.player_id ORDER BY value1 DESC,value2 DESC,value3 DESC limit 1)as value3 FROM按照值1对GROUP BY player_id进行排序DESC,value2 DESC,value3 DESC或类似的东西
SELECT * FROM得分 其中id =(SELECT id FROM得分b其中a.player_id = b.player_id ORDER BY value1 DESC,value2 DESC,value3 DESC限制1) GROUP BY player_id按值排序1 DESC,v alue2 DESC,value3 DESC
I have a table called Scores which contains columns: id, player_id, value1, value2, value3 and date.
The table has next following content:
+------+-----------+--------+--------+--------+------------+ | id | player_id | value1 | value2 | value3 | date | +------+-----------+--------+--------+--------+------------+ | 1 | 1 | 10 | 0 | 0 | 2012-08-02 | +------+-----------+--------+--------+--------+------------+ | 2 | 2 | 15 | 1 | 0 | 2012-08-03 | +------+-----------+--------+--------+--------+------------+ | 3 | 3 | 9 | 0 | 0 | 2012-08-04 | +------+-----------+--------+--------+--------+------------+ | 4 | 1 | 11 | 0 | 0 | 2012-08-05 | +------+-----------+--------+--------+--------+------------+ | 5 | 2 | 16 | 2 | 0 | 2012-08-06 | +------+-----------+--------+--------+--------+------------+ | 6 | 2 | 15 | 0 | 0 | 2012-08-07 | +------+-----------+--------+--------+--------+------------+I am trying to get a query which returns the best highscore of each player ordered by the value in "value1, value2, value3". Value1 is the field with more importance, value2 medium importance and value3 minor importance, example:
value1 = 15 value1 = 15 value2 = 1 is greater than -> value2 = 0 value3 = 0 value3 = 1The expected result from the query which I need is:
+------+-----------+--------+--------+--------+------------+ | id | player_id | value1 | value2 | value3 | date | +------+-----------+--------+--------+--------+------------+ | 5 | 2 | 16 | 2 | 0 | 2012-08-06 | +------+-----------+--------+--------+--------+------------+ | 4 | 1 | 11 | 0 | 0 | 2012-08-05 | +------+-----------+--------+--------+--------+------------+ | 3 | 3 | 9 | 0 | 0 | 2012-08-04 | +------+-----------+--------+--------+--------+------------+I'm trying with MAX, DISTINCT, GROUP BY and sub-queries but I don't get the correct result. Basically it is the next query but picking the first row of each "group":
SELECT id, player_id, value1, value2, value3 FROM scores ORDER BY value1 DESC, value2 DESC, value3 DESC------EDIT 1-------
eggyal's answer works fine but, maybe, the performance is not too good. I need to benchmark his solution against large database to check response times.
I have had an idea (and possible solution). The solution consists adding new boolean column which says if that score is the best score of that player or not. This way I need to check if the new score is better than the best old score of that player when I'm adding new score into DB, if it is I need to mark the flag as false in the old best score and as true in the new score. This gives me a way to retrieve the best score of each player directly (simple query like SELECT ... FROM .... ORDER BY).
------EDIT 2-------
weicap's answer is the fastest solution. I don't know why but his query is twice more faster than eggyal's query.
------EDIT 3------- I was wrong, weicap's query is more faster if the query was cached previously, if it wasn't the query takes ten or more seconds. In change, weicap's answer always takes 300-400ms against 80.000 rows.
解决方案you can try this
SELECT player_id, (SELECT value1 FROM Scores b where a.player_id=b.player_id ORDER BY value1 DESC, value2 DESC, value3 DESC limit 1) as value1, (SELECT value2 FROM Scores b where a.player_id=b.player_id ORDER BY value1 DESC, value2 DESC, value3 DESC limit 1) as value2, (SELECT value3 FROM Scores b where a.player_id=b.player_id ORDER BY value1 DESC, value2 DESC, value3 DESC limit 1) as value3 FROM Scores a GROUP BY player_id order by value1 DESC, value2 DESC, value3 DESCor something like
SELECT * FROM Scores a where id =(SELECT id FROM Scores b where a.player_id=b.player_id ORDER BY value1 DESC, value2 DESC, value3 DESC limit 1) GROUP BY player_id order by value1 DESC, value2 DESC, value3 DESC
更多推荐
选择每个球员的最佳分数
发布评论