mysql - 将 7 个表中的 8 个复杂查询合并为一个查询

Question

我需要通过将来自 7 个不同表的 8 个查询合并到一个查询中来创建一个复杂的查询，以创建完整的报告。我可以做基本的组合，但这个查询让我变得更好。我已经浏览了网络以查找复杂的联接查询，但我没有找到正确的...

所有表格都通过“plant_id”链接，但一些数据是最新条目，一些是当月的计算条目等。下面的查询，所有这些都可以完美地独立工作，但我必须将它们组合起来分为以下内容:

[ plant_id, plant_make_id, plant_model ],[ current_plant_hrs, plant_hours_total ],[ plant_error_report ],[ plant_service_next ],[ plant_status_ddl_id ],[ 柴油总使用量, 消耗量 ],[ location_id ]

下面是分割+单独的查询:-

1 plant [ plant_id, plant_make_id, plant_model ],       
SELECT `plant_id`,`plant_make_id`,`plant_model`
 FROM `plant`

2 plant_hrs [ current_plant_hrs, plant_hours_total  ],           
A
SELECT `plant_hrs_stop`
FROM `plant_hrs` s1
WHERE `plant_hrs_date`=(SELECT MAX(s2.`plant_hrs_date`)
FROM `plant_hrs` s2
WHERE s1.`plant_id` = s2.`plant_id`)
ORDER BY `plant_id`
B
SELECT (MAX(`plant_hrs_stop`)- MIN(`plant_hrs_start`) )total_hrs
FROM`plant_hrs` 
WHERE MONTH(`plant_hrs_date`)= MONTH( CURRENT_DATE ) 
GROUP BY`plant_id`

3 plant_error [ plant_error_report ],               
SELECT `plant_error_report`
FROM `plant_error` s1
WHERE `plant_error_date`=(SELECT MAX(s2.`plant_error_date`)
FROM `plant_error` s2
WHERE s1.`plant_id` = s2.`plant_id`)
ORDER BY `plant_id`

4 plant_service [ plant_service_next ],             
SELECT `plant_service_hrs` , `plant_service_next`
FROM `plant_service` s1
WHERE `plant_service_date`=(SELECT MAX(s2.`plant_service_date`)
FROM `plant_service` s2
WHERE s1.`plant_id` = s2.`plant_id`)
ORDER BY `plant_id`

5 plant_status [ plant_status_ddl_id ],              
SELECT `plant_status_ddl_id`
FROM `plant_status`
WHERE `plant_status_date` = ( CURRENT_DATE ) - 1
GROUP BY `plant_id

6 diesel [ total diesel used, consumption ],            // 'plant_id = diesel_vehicle_no' 
SELECT (SUM(`diesel_qty`) )total_d
FROM`diesel` 
WHERE MONTH(`diesel_date`)= MONTH( CURRENT_DATE ) 
GROUP BY `diesel_vehicle_no`

7 plant_location [ location_id ]                    
SELECT `location_id` FROM `plant_location`
ORDER BY `plant_id`

任何帮助将不胜感激...

Answer 1

好的，让我们看一下:我们将首先简单地组合您的查询 - 尽管还有很大的优化空间。

查询 1 和 7 不执行任何操作 - 因此我们将使用它们的基表 - 其余部分我们按“原样”进行

SELECT
  `plant`.`plant_id`,
  IFNULL(`qry2A`.`plant_hrs_stop`,''),
  IFNULL(`qry2B`.`total_hrs`,''),
  IFNULL(`qry3`.`plant_error_report`,''),
  IFNULL(`qry4`.`plant_service_next`,''),
  IFNULL(`qry5`.`plant_status_ddl_id`,''),
  IFNULL(`qry6`.`total_d`,''),
  IFNULL(`plant_location`.`location_id`'')
FROM
  -- Query 1: Use table
  `plant`
  -- Query 2A: Use as-is, but with the plant_id field and without the ORDER BY
  LEFT JOIN (
    SELECT `plant_hrs_stop`, s1.`plant_id` AS `plant_id`
    FROM `plant_hrs` s1
    WHERE `plant_hrs_date`=(SELECT MAX(s2.`plant_hrs_date`)
    FROM `plant_hrs` s2
    WHERE s1.`plant_id` = s2.`plant_id`)
  ) AS `qry2A` ON `plant`.`plant_id`=`qry2A`.`plant_id`
  -- Query 2B: Use as-is, but with the plant_id field
  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 `qry2B` ON `plant`.`plant_id`=`qry2B`.`plant_id`
  -- Query 3: Use as-is, but with the plant_id field and without the ORDER BY
  LEFT JOIN (
    SELECT `plant_error_report`,s1.`plant_id` AS `plant_id`
    FROM `plant_error` s1
    WHERE `plant_error_date`=(SELECT MAX(s2.`plant_error_date`)
    FROM `plant_error` s2
    WHERE s1.`plant_id` = s2.`plant_id`)
  ) AS `qry3` ON `plant`.`plant_id`=`qry3`.`plant_id`
  -- Query 4: Use as-is, but with the plant_id field and without the ORDER BY
  LEFT JOIN (
    SELECT `plant_service_hrs` , `plant_service_next`,s1.`plant_id` AS `plant_id`
    FROM `plant_service` s1
    WHERE `plant_service_date`=(SELECT MAX(s2.`plant_service_date`)
    FROM `plant_service` s2
    WHERE s1.`plant_id` = s2.`plant_id`)
  ) AS `qry4` ON `plant`.`plant_id`=`qry4`.`plant_id`
  -- Query 5: Use as-is, but with the plant_id field
  LEFT JOIN (
    SELECT `plant_status_ddl_id`,`plant_id`
    FROM `plant_status`
    WHERE `plant_status_date` = ( CURRENT_DATE ) - 1
    GROUP BY `plant_id
  ) AS `qry5` ON `plant`.`plant_id`=`qry5`.`plant_id`
  -- Query 6: Use as-is, but with the plant_id field
  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 `qry6` ON `plant`.`plant_id`=`qry6`.`plant_id`
  -- Query 7: Use table
  LEFT JOIN `plant_location` ON `plant`.`plant_id`=`plant_location`.`plant_id`
ORDER BY
  `plant`.`plant_id`

编辑:按照 @LuisSiquot 的建议使用 LEFT 而不是 INNER 连接

编辑:在评论中请求后添加了 IFNULL()