mysql - GROUP BY + HAVING 忽略行

Question

基本上我想要的是我可以选择所有具有记录保持者和最佳时间的比赛记录。我查找了类似的查询，并设法找到了 3 个比其他查询更快的查询。

问题是它完全忽略了 userid 2 拥有记录的种族。

这些是我的表、索引和一些示例数据:

CREATE TABLE `races` (
 `raceid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 PRIMARY KEY (`raceid`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
 `userid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 PRIMARY KEY (`userid`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `race_times` (
 `raceid` smallint(5) unsigned NOT NULL,
 `userid` mediumint(8) unsigned NOT NULL,
 `time` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`raceid`,`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `races` (`raceid`, `name`) VALUES
(1, 'Doherty'),
(3, 'Easter Basin Naval S'),
(5, 'Flint County'),
(6, 'Fort Carson'),
(4, 'Glen Park'),
(2, 'Palomino Creek'),
(7, 'Tierra Robada');

INSERT INTO `users` (`userid`, `name`) VALUES
(1, 'Player 1'),
(2, 'Player 2');

INSERT INTO `race_times` (`raceid`, `userid`, `time`) VALUES
(1, 1, 51637),
(1, 2, 50000),
(2, 1, 148039),
(3, 1, 120516),
(3, 2, 124773),
(4, 1, 101109),
(6, 1, 89092),
(6, 2, 89557),
(7, 1, 77933),
(7, 2, 78038);

所以如果我运行这两个查询:

SELECT rt1.raceid, r.name, rt1.userid, p.name, rt1.time
FROM race_times rt1
LEFT JOIN users p ON (rt1.userid = p.userid)
JOIN races r ON (r.raceid = rt1.raceid)
WHERE rt1.time = (SELECT MIN(rt2.time) FROM race_times rt2 WHERE rt1.raceid = rt2.raceid)
GROUP BY r.name;

或者..

SELECT rt1.*, r.name, p.name
FROM race_times rt1
LEFT JOIN users p ON p.userid = rt1.userid
JOIN races r ON r.raceid = rt1.raceid
WHERE EXISTS (SELECT NULL FROM race_times rt2 WHERE rt2.raceid = rt1.raceid
GROUP BY rt2.raceid HAVING MIN(rt2.time) >= rt1.time);

我收到如下所示的正确结果:

raceid | name                 | userid | name     | time   |
-------+----------------------+--------+----------+--------|
1      | Doherty              | 2      | Player 2 | 50000  |
3      | Easter Basin Naval S | 1      | Player 1 | 120516 |
6      | Fort Carson          | 1      | Player 1 | 89092  |
4      | Glen Park            | 1      | Player 1 | 101109 |
2      | Palomino Creek       | 1      | Player 1 | 148039 |
7      | Tierra Robada        | 1      | Player 1 | 77933  |

这是错误的查询:

SELECT rt.raceid, r.name, rt.userid, p.name, rt.time
FROM race_times rt
LEFT JOIN users p ON p.userid = rt.userid
JOIN races r ON r.raceid = rt.raceid
GROUP BY r.name
HAVING rt.time = MIN(rt.time);

结果是这样的:

raceid | name                 | userid | name     | time   |
-------+----------------------+--------+----------+--------|
3      | Easter Basin Naval S | 1      | Player 1 | 120516 |
6      | Fort Carson          | 1      | Player 1 | 89092  |
4      | Glen Park            | 1      | Player 1 | 101109 |
2      | Palomino Creek       | 1      | Player 1 | 148039 |
7      | Tierra Robada        | 1      | Player 1 | 77933  |

如您所见，比赛“Doherty”(比赛 ID:1)归“玩家 2”(用户 ID:2)所有，它不会与其他比赛记录(全部由用户 ID 1 所有)一起显示.有什么问题？

问候，

Answer 1

有一个后置过滤器。 query得到所有的结果，然后根据having进一步过滤。 GROUP BY 根据组压缩行，这为您提供每组中的第一个条目。由于玩家 1 是比赛 1 的第一个参赛者，因此这就是 HAVING 正在处理的结果。然后将其过滤掉，因为它的时间不等于组结果的 MIN(time)。

这就是您发布的其他人使用子查询的原因。我个人更喜欢第一个例子，对我来说它更容易阅读。在性能方面它们应该是相同的。

虽然尝试避免在 where 子句中使用子查询是个不错的主意，但当您可以使用 JOIN 完成相同的结果时，这最有效。其他时候无法使用 JOIN 获得结果，需要子查询。