如何将外部联接与子查询和groupby一起使用

Question

工具：MySQL Workbench 6.3
版本：MySQL 5.7

SELECT *
FROM cars as a, battery_log as b
WHERE a.user_seq = 226 AND a.seq = b.car_seq
AND b.created = ( SELECT MAX(created) FROM battery_log WHERE car_seq = a.seq )
GROUP BY car_type
ORDER BY a.created DESC;

我想将此查询转换为外部联接。

通过在"cars"表中搜索user_seq我需要在相应的汽车表的一对多关系中获得电池日志的最新值。

有时电池日志的值与car-seq不匹配，因此它从表a和表b的连接过程中被截断。我该如何解决此问题？

SELECT a.*, b.battery 
FROM cars as a 
LEFT OUTER JOIN battery_log as b ON a.seq = b.car_seq
LEFT OUTER JOIN ( SELECT MAX(created) FROM battery_log WHERE a.seq = b.car_seq) as c
ON b.created = c.MAX(created)
WHERE a.user_seq = 226
GROUP BY car_type 
ORDER BY a.created DESC

我试图用这种方式修复它，但我得到了以下错误：

错误代码：1054，"此处子句"中的未知列".seq">

Answer 1

我这样解决了这个问题。

SELECT *
FROM cars as a
LEFT OUTER JOIN battery_log as b ON a.seq = b.car_seq 
AND b.created = (SELECT MAX(created) FROM battery_log WHERE car_seq = b.car_seq)
WHERE a.user_seq = 226
GROUP BY car_type
ORDER BY a.created DESC;

在LEFT OUTER JOIN之后。。。ON时，用AND给出一个附加条件，并根据该条件执行查询。