内部连接两个表，只显示最新记录(mySQL)

Question

我有两个父表，它们在子表中创建了一个复合表(compresults)

我想得到每名运动员的最新记录(只有1项)。

我知道答案是按照选择最大日期的竞争方式，只以这样的方式显示，但是我一直在尝试这样做，但我无法正确地设置格式。

家长表(运动员) +---------+-------+ | athlete | name | +---------+-------+ | 1 | James | | 2 | Gemma | +---------+-------+

家长表(比赛) +-----+----------+----------+ | cid | compname | compdate | +-----+----------+----------+ | 1 | Comp A | 2015 | | 2 | Comp B | 2014 | +-----+----------+----------+

子表(结果) +---------+-----+--------+ | athlete | cid | result | +---------+-----+--------+ | 1 | 1 | 500 | | 1 | 2 | 550 | | 2 | 2 | 450 | +---------+-----+--------+

预期结果：+-------+----------+--------+ | name | compname | result | +-------+----------+--------+ | James | Comp A | 500 | | Gemma | Comp B | 450 | +-------+----------+--------+

知道吗？谢谢!

Answer 1

我相信这会得到你想要的结果。

SELECT a.`name`, b.`compname`, c.`result`
FROM `compresults` c
INNER JOIN `competitions` b ON b.cid = c.cid
INNER JOIN `athletes` a ON c.`athlete` = a.`athlete`
WHERE b.`compdate` = (
                                    SELECT co.`compdate` 
                                    FROM `competitions` co INNER JOIN `compresults` cr ON cr.`cid` = co.`cid` 
                                    WHERE cr.`athlete` = a.`athlete`
                                    ORDER BY co.`compdate` DESC LIMIT 1
                    )

Answer 2

试着使用

select b.name,c.compname,a.result from compresults a left join athletes 
b on (a.athlete)=b.athlete
left join competitions c on (a.cid)=c.cid