这是我的数据集和当前代码:http://sqlfiddle.com/#!2/f9a605/3
| id | courseid | username | day | score |
--------------------------------------------
| 1 | 2 | tim | may 5 | 85 |
| 2 | 2 | mike | may 6 | 86 |
| 3 | 2 | tim | may 7 | 82 |
| 4 | 3 | tim | may 8 | 80 |
| 5 | 2 | mike | may 9 | 79 |
| 6 | 2 | joe | may 10| 81 |
我想选择每个用户的最低分数的值,其中courseid=2按最低分数排序,所以结果应该看起来像:
| mike | may 9 | 79 |
| joe | may 10 | 81 |
| tim | may 8 | 82 |
我当前的代码是这样的:
SELECT courseid, username, day, MIN(score) FROM result where courseid = '2' GROUP BY username order by MIN(score) limit 10
我当前的结果是这样的:
| mike | may 6 | 79 |
| joe | may 10 | 81 |
| tim | may 5 | 82 |
日子不对。
当courseid =2时,我如何得到那个人的最低分数对应的正确日期?
这可以在两种情况下使用self join来完成,一种是用户名,另一种是最低分数
SELECT r.courseid, r.username, r.`day`,r.score
FROM result r
JOIN (SELECT username, MIN(score) score
FROM result
where courseid = '2'
GROUP BY username) r1
ON(r.username = r1.username and r.score = r1.score)
order by r.score
limit 10
同时在你的预期结果时间应该有5月7日的日期
Fiddle Demo
您可以使用子查询:
select courseid, username, day, score
from result x
where score = (select min(y.score)
from result y
where y.username = x.username
and x.courseid = y.courseid)
and courseid = 2
order by score
小提琴:
http://sqlfiddle.com/!2/f9a605/36/0