mysql - 使用带有 GROUP BY 和 INNER JOIN 的 MIN() 函数删除重复行

Question

我试图在加入“运动员表”时为“结果表”中的每个人列出唯一/不同的 MIN(时间)，但我得到了重复项。

这是一些示例数据(我正在运行 MySql 5.7)

结果表
+----------+-----------+---------+----------+-------+-------------+------------+
| resultID | athleteID | eventID | ageGroup | time | venue | date |
+----------+-----------+---------+----------+-------+-------------+------------+
| 1 | 10 | 1 | MS | 10.20 | Tokyo | 06-06-2019 |
| 2 | 11 | 1 | MS | 10.24 | London | 03-08-2019 |
| 3 | 10 | 1 | MS | 10.20 | Los Angeles | 01-11-2019 |
| 4 | 13 | 1 | MS | 10.29 | Glasgow | 28-10-2019 |
| 5 | 14 | 1 | MS | 10.32 | Oslo | 16-07-2019 |
| ... | ... | ... | ... | ... | ... | ... |
| ... | ... | ... | ... | ... | ... | ... |
+----------+-----------+---------+----------+-------+-------------+------------+

运动员表
+-----------+-----------+----------+--------+-------------+
| athleteID | nameFirst | nameLast | gender | dateOfBirth |
+-----------+-----------+----------+--------+-------------+
| 10 | Bill | Smith | MS | 10-11-2000 |
| 11 | John | Brown | MS | 1-08-1999 |
| 12 | Steve | Jones | MS | 16-01-1997 |
| 13 | Alan | Green | MS | 21-07-2001 |
| 14 | Paul | Black | MS | 27-10-2000 |
| ... | ... | ... | ... | ... |
| ... | ... | ... | ... | ... |
+-----------+-----------+----------+--------+-------------+


我尝试了以下代码 - 它似乎带来了正确的结果集，但返回了重复的值。 Bill Smith 跑了两次 10.20，但我只需要展示其中一次。

已尝试在两个 SELECT 上使用 DISTINCT 函数，但没有成功 - 这就是我所拥有的:

SELECT *
FROM results
INNER JOIN (
SELECT athleteID, nameFirst, nameLast, MIN(time) as minTime
FROM results
INNER JOIN athletes USING(athleteID)
WHERE eventID = '1'
AND ageGroup IN('MS')
AND YEAR(results.date) = '2019'
GROUP BY athleteID<br/>
) AS child ON (results.athleteID = child.athleteID) AND (results.time = minTime)
HAVING YEAR(results.date) = '2019'
ORDER BY minTime ASC


我得到了这个结果
+-------+-----------+----------+-------------+------------+
| time | nameFirst | nameLast | venue | date |
+-------+-----------+----------+-------------+------------+
| 10.20 | Bill | Smith | Tokyo | 06-06-2019 |
| 10.20 | Bill | Smith | Los Angeles | 01-11-2019 |
| 10.24 | John | Brown | London | 03-08-2019 |
| 10.29 | Steve | Jones | Glasgow | 28-10-2019 |
| 10.32 | Alan | Green | Oslo | 16-07-2019 |
| ... | ... | ... | ... | ... |
| ... | ... | ... | ... | ... |
+-------+-----------+----------+-------------+------------+
如您所见，Bill Smith(10.20 - 洛杉矶)的附加结果也出现了。我需要将其省略，并且每个运动员只显示 1 个结果 - 如下所示。

期望的结果
+-------+-----------+----------+---------+------------+
| time | nameFirst | nameLast | venue | date |
+-------+-----------+----------+---------+------------+
| 10.20 | Bill | Smith | Tokyo | 06-06-2019 |
| 10.24 | John | Brown | London | 03-08-2019 |
| 10.29 | Steve | Jones | Glasgow | 28-10-2019 |
| 10.32 | Alan | Green | Oslo | 16-07-2019 |
+-------+-----------+----------+---------+------------+


关于我可以尝试什么有什么建议吗？

提前致谢..

Answer 1

在这种情况下，您的运动员具有相同的最短时间，您也需要在外部选择中使用最短日期

SELECT  r.athleteID, r.nameFirst, r.nameLast, min(r.date), child.minTime
FROM results r
INNER JOIN (
    SELECT athleteID, nameFirst, nameLast
      , MIN(time) as minTime
    FROM results
    INNER JOIN athletes USING(athleteID)
    WHERE eventID = '1'
    AND ageGroup IN('MS')
    AND YEAR(results.date) = '2019'
    GROUP BY athleteID
) AS child ON (r.athleteID = child.athleteID) AND (r.time = minTime)
WHERE YEAR(r.date) = '2019'
GROUP BY r.athleteID, child.minTime
ORDER BY minTime ASC