mysql - 如何从 2x LEFT JOIN 计算字段数据

Question

我需要计算我们工厂的燃料消耗量。我的查询是 LEFT JOIN，第一个“qry1”计算工厂当月运行的总小时数，“qry2”计算每个工厂使用的柴油总量。

这些独立运行良好，但添加“qry3”，这应该将“qry2”的答案与“qry1”分开，并给出平均柴油消耗量，但我不知道如何使用每个输出这些 LEFT JOIN 的并使用结果进行(应该很容易)计算:

SELECT 
 `plant`.`plant_id`,
 `qry1`.`total_hrs`, 
 `qry2`.`total_d`, 
 `qry3`.`consumption`

FROM `plant` 

  LEFT JOIN ( 
   SELECT (MAX(`plant_hrs_stop`)- MIN(`plant_hrs_start`) ) AS total_hrs, `plant_id` 
   FROM`plant_hrs`  
   WHERE MONTH(`plant_hrs_date`)= MONTH( CURRENT_DATE )  
   GROUP BY`plant_id` 
  ) AS `qry1` ON `plant`.`plant_id`=`qry1`.`plant_id` 

  LEFT JOIN ( 
   SELECT (SUM(`diesel_qty`) ) AS total_d, `diesel_vehicle_no` AS `plant_id` 
   FROM`diesel`  
   WHERE MONTH(`diesel_date`)= MONTH( CURRENT_DATE )  
   GROUP BY `diesel_vehicle_no` 
  ) AS `qry2` ON `plant`.`plant_id`=`qry2`.`plant_id` 

  LEFT JOIN ( 
   SELECT (`qry2`.`total_d` / `qry1`.`total_hrs`) AS consumption,    
   FROM `qry1`, `qry2`  
   GROUP BY `plant_id` 
  ) AS `qry3` ON `plant`.`plant_id`=`qry3`.`plant_id`

ORDER BY `plant`.`plant_id` 

我不断收到错误，我已经尝试了几次搜索，但一直都是空的...

Answer 1

您不需要最后一次加入。并确保qry1.total_hrs <> 0 :

SELECT 
 `plant`.`plant_id`,
 `qry1`.`total_hrs`, 
 `qry2`.`total_d`, 
 `qry3`.`consumption`,
  if(COALESCE(`qry1`.`total_hrs`,0)<>0,'qry2`.`total_d` / `qry1`.`total_hrs`,NULL) AS consumption

FROM `plant` 

  LEFT JOIN ( 
   SELECT (MAX(`plant_hrs_stop`)- MIN(`plant_hrs_start`) ) AS total_hrs, `plant_id` 
   FROM`plant_hrs`  
   WHERE MONTH(`plant_hrs_date`)= MONTH( CURRENT_DATE )  
   GROUP BY`plant_id` 
  ) AS `qry1` ON `plant`.`plant_id`=`qry1`.`plant_id` 

  LEFT JOIN ( 
   SELECT (SUM(`diesel_qty`) ) AS total_d, `diesel_vehicle_no` AS `plant_id` 
   FROM`diesel`  
   WHERE MONTH(`diesel_date`)= MONTH( CURRENT_DATE )  
   GROUP BY `diesel_vehicle_no` 
  ) AS `qry2` ON `plant`.`plant_id`=`qry2`.`plant_id` 

ORDER BY `plant`.`plant_id`