sql - MySQL 中的表 JOIN 和聚合问题

Question

我有三个表。此查询将写下正确的答案(btv.id_user 的 x 行以及适当的 btv.cas 和 race.id_zavod

SELECT `btv.id_user`, `btv.id_zavod`,`btv.cas`
FROM `btv`
JOIN `btu` ON `btv.id_user` = `btu.id_user`
JOIN `race` ON 'btv.id_zavod' = `race.id_zavod`
WHERE `race.type` = '8' AND `btv.id_user` = '607'

结果:

| 607 |  512 | 03:15:58 |  
| 607 |  730 | 03:01:18 |  
| 607 |  164 | 03:07:26 |  
| 607 |  767 | 02:58:31 |  
| 607 | 1147 | 03:06:47 |  
| 607 | 1149 | 03:09:41 |  
| 607 | 1178 | 03:24:20 | 

但是当我尝试通过 id_user 将它聚合到一行时，它返回正确的 min btv.cas 但错误的加入错误的 race.id_zavod

SELECT `btv.id_user`, `btv.id_zavod`, MIN( `btv.cas` )
FROM `btv`
JOIN `btu` ON `btv.id_user` = `btu.id_user`
JOIN `race` ON 'btv.id_zavod' = `race.id_zavod`
WHERE `race.type` = '8' AND `btv.id_user` = '607'
GROUP BY `btv.id_user`

结果:

| 607 | 512 | 02:58:31 |

Answer 1

您编写的查询:

SELECT `btv.id_user`, `btv.id_zavod`, MIN( `btv.cas` )
FROM `btv`
JOIN `btu` ON `btv.id_user` = `btu.id_user`
JOIN `race` ON 'btv.id_zavod' = `race.id_zavod`
WHERE `race.type` = '8' AND `btv.id_user` = '607'
GROUP BY `btv.id_user`

不会运行。您需要通过 id_zavod 或其他方式进行分组。你能告诉我们你真正运行的查询是什么吗？您期望得到什么样的结果？