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