选择每个球员的最佳分数 [英] Selecting the best score per player
问题描述
我有一个名为 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 = 1
The 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 DESC
or 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
这篇关于选择每个球员的最佳分数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
