MySql 对来自这两个或多个子查询的字段进行分组和求和

Question

我有两个问题。第一个查询

SELECT sum(pre.manhour) as manhour
from repair_estimate re

left join repair_detail_pre pre
ON pre.repair_estimate_id = re.id

WHERE re.id  = 3426
+---------+
| manhour |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

第二个查询是这样的:

SELECT SUM(first.manhour) as manhour
from repair_estimate re

left join repair_detail_first_additional first
ON first.repair_estimate_id = re.id

WHERE re.id  = 3426

+--------------------+
| manhour            |
+--------------------+
|               1.25 |
+--------------------+
1 row in set (0.00 sec)

如何对来自这两个子查询的工时字段和 SUM 字段进行分组。

因为第一个查询给出 null 并且第二个查询是 1.25，所以 sum 是 1,25。

预期的结果是

+--------------------+
| manhour            |
+--------------------+
|               1.25 |
+--------------------+

请指教

Answer 1

您可以使用选择联合来获得总结果

  select sum(manhour) as total 
  from (
        SELECT sum(pre.manhour) as manhour
        from repair_estimate re
        left join repair_detail_pre pre
        ON pre.repair_estimate_id = re.id
        WHERE re.id  = 3426
        union all 
        SELECT SUM(first.manhour) 
        from repair_estimate re
        left join repair_detail_first_additional first
        ON first.repair_estimate_id = re.id
        WHERE re.id  = 3426
    ) t