mysql多表聚合_mysql full join 三张表聚合查询

Vehicle车辆表，A，B两张表是车辆加油信息表，现在需要以Vehicle表为主表查询一个月中各车分别加了多少油以及加油次数，之前在sqlserver中用full查询出来了，但是现在因为数据库改成m...

Vehicle车辆表，A，B 两张表是车辆加油信息表，现在需要以Vehicle表为主表查询一个月中各车分别加了多少油以及加油次数，之前在sqlserver中用full查询出来了，但是现在因为数据库改成mysql所以出现了问题，希望大神能帮帮我

SELECT

v.licensePlateNumber AS licensePlateNo,

r.sysSumOil,

r.sysCount,

r.excelSumOil,

r.excelCount

FROM

Vehicle AS v

LEFT JOIN (

SELECT

*

FROM

(

SELECT

v.licensePlateNo,

SUM(v.oil) AS sysSumOil,

COUNT(v.licensePlateNo) AS sysCount

FROM

VehicleInspection AS v

WHERE

date_format(v.oiltime, '%Y-%m') = '2018-02'

GROUP BY

v.licensePlateNo

) AS v

FULL JOIN (

SELECT

o.licensePlateNo AS num2,

SUM(o.litre) AS excelSumOil,

COUNT(o.licensePlateNo) AS excelCount

FROM

OilDetail AS o

WHERE

date_format(o.tradingHours, '%Y-%m') = '2018-02'

GROUP BY

o.licensePlateNo

) AS o ON o.num2 = v.licensePlateNo

) AS r ON r.licensePlateNo = v.licensePlateNumber

OR r.num2 = v.licensePlateNumber

展开