mysql - 如何使用 MYSQL 编写查询

Question

我正在处理工作正常的查询，但它在某些情况下不工作。理想情况下，它应该可以工作，但我不知道我哪里出错了。举个例子，有 capital machine 和 qc 三个表

查询:

select all_dates.value1 as `Date`, coalesce(`Outward1(B_Qty)`,`Outward2(B_Qty)`,`Outward3(B_Qty)`, '0') as `Outward` 
from 
(select distinct C_Date as value1 from capital where P2_Goods like '750%' and Monthname(C_Date)='April'  
union all
select distinct M_Date from machine  where J_Id like '%750' and Monthname(M_Date)='April' 
union all
select distinct QC_Date from qc where QC_Name like '750%'  and Monthname(QC_Date)='April'  
) as all_dates
left join( select C_Date, sum(PR) AS `Outward1(B_Qty)` 
from capital where P2_Goods like '750%' and Monthname(C_Date)='April' group by C_Date) 
as f on f.C_Date = all_dates.value1 
left join( select M_Date, (sum(PR)+sum(C_Skip)) AS `Outward2(B_Qty)` 
from machine  where J_Id like '%750' and Monthname(M_Date)='April'  group by M_Date) 
as h on  h.M_Date = all_dates.value1 
left join( select QC_Date, sum(PR) AS `Outward3(B_Qty)` 
from qc where QC_Name like '750%' and Monthname(QC_Date)='April' group by QC_Date) 
as k on  k.QC_Date = all_dates.value1 group by all_dates.value1

查询的主要目的是，每个表中按日期分组的 PR 和 C_Skip 列的总计
现在，如果两个不同的表上有相同的日期，那么它应该添加 PR ，但在这种情况下它没有添加。这就是问题所在。
请帮助我。提前致谢。

Answer 1

试试这个。

select date1,sum(outward) as outward from
    (
    select c_date as date1,pr as outward from capital
    union all
    select m_date as date1,pr+c_skip  as outward from machine
    union all
    select qc_date as date1,pr  as outward from qc
    ) t
group by date1