I'm working on a track and field ranking database in MySQL/PHP5 whereby I'm struggling to find the best way to query results per unique athlete by highest value.
我正在开发MySQL/PHP5中的一个田径排名数据库，在这个数据库中，我很难找到最好的方法来查询每个运动员的最高数值。
just
只是
SELECT distinct name, event
FROM results
sample database
示例数据库
name | event | result
--------------------------
athlete 1 | 40 | 7.43
athlete 2 | 40 | 7.66
athlete 1 | 40 | 7.33
athlete 1 | 60 | 9.99
athlete 2 | 60 | 10.55
so let's say that in this case I'd like to rank the athletes on the 40m dash event by best performance I tried
假设在这种情况下，我想用我所尝试过的最好成绩来给4000米短跑项目的运动员排名
SELECT distinct name, event
FROM results
WHERE event = 40
ORDER by result DESC
but the distinct only leaves the first performance (7.43) of the athlete which isn't the best (7.33). Is there an easy way other than creating a temp table first whereby the results are ordered first and performing a select on the temp table afterwards?
但是唯一不同的是，运动员的第一次表现(7.43)不是最好的(7.33)。除了先创建一个临时表，先对结果排序，然后在临时表上执行select之外，还有什么简单的方法吗?
2 个解决方案
#1
4
You might be interested in group by:
你可能对团体感兴趣:
SELECT name, min(result) as result
FROM results
WHERE event = 40
GROUP BY name
This gives you the best result per athlete.
这是每个运动员的最佳成绩。
As suggested by spencer, you can also order the list by appending this:
根据斯宾塞的建议，你也可以通过以下方式订购:
ORDER BY min(result) ASC
#2
-1
The problem is that the columns used in the ORDER BY aren't specified in the DISTINCT. To do this, you need to use an aggregate function to sort on, and use a GROUP BY to make the DISTINCT work.
问题是，ORDER BY中使用的列没有在不同的列中指定。要做到这一点，您需要使用聚合函数来排序，并使用一个组来完成不同的工作。
SELECT distinct name, event
FROM results
WHERE event = 40
GROUP BY name
ORDER by result DESC
#1
4
You might be interested in group by:
你可能对团体感兴趣:
SELECT name, min(result) as result
FROM results
WHERE event = 40
GROUP BY name
This gives you the best result per athlete.
这是每个运动员的最佳成绩。
As suggested by spencer, you can also order the list by appending this:
根据斯宾塞的建议，你也可以通过以下方式订购:
ORDER BY min(result) ASC
#2
-1
The problem is that the columns used in the ORDER BY aren't specified in the DISTINCT. To do this, you need to use an aggregate function to sort on, and use a GROUP BY to make the DISTINCT work.
问题是，ORDER BY中使用的列没有在不同的列中指定。要做到这一点，您需要使用聚合函数来排序，并使用一个组来完成不同的工作。
SELECT distinct name, event
FROM results
WHERE event = 40
GROUP BY name
ORDER by result DESC