mysql - 合并 2 个左连接

Question

我的数据库中有 3 个表。这是它的样子:

tbl_生产:

+--------+------------+-----+-------+  
| id_pro | date       | qty | stock | 
+--------+------------+-----+-------+  
| 1      | 2017-09-14 | 100 |  0    |
| 2      | 2017-09-15 | 150 |  0    |
| 3      | 2017-09-16 | 140 |  0    |

输出:

+--------+------------+-----+  
| id_out | date       | qty |  
+--------+------------+-----+
| 1      | 2017-09-14 | 50  |  
| 2      | 2017-09-14 | 50  |  
| 3      | 2017-09-15 | 50  |  
| 4      | 2017-09-15 | 50  |
| 5      | 2017-09-15 | 50  |
| 6      | 2017-09-16 | 40  |
| 7      | 2017-09-16 | 50  |
| 8      | 2017-09-16 | 50  |

tbl_return:

+--------+------------+-----+  
| id_ret | date       | qty |
+--------+------------+-----+
| 1      | 2017-09-14 |  8  |  
| 2      | 2017-09-14 |  0  |  
| 3      | 2017-09-15 |  4  |  
| 4      | 2017-09-15 |  0  |
| 5      | 2017-09-15 |  3  |
| 6      | 2017-09-16 |  3  |
| 7      | 2017-09-16 |  2  |
| 8      | 2017-09-16 |  0  |

我想加入这个查询

SELECT TRUNCATE(COALESCE(SUM(tbl_out.qty),0),0) AS Out, tbl_production.date FROM tbl_production LEFT JOIN tbl_out
    ON (tbl_production.date = tbl_out.date) GROUP BY tbl_production.date;

与

SELECT TRUNCATE(COALESCE(SUM(tbl_return.qty),0),0) AS ret FROM tbl_production LEFT JOIN tbl_return ON (tbl_production.date = tbl_return.date)GROUP BY tbl_production.date;

我想看到的结果是这样的

+--------+------------+-----+  
| out    | date       | ret | 
+--------+------------+-----+  
| 100    | 2017-09-14 | 8   |
| 150    | 2017-09-15 | 7   |
| 140    | 2017-09-16 | 5   | 

我怎样才能做到这一点？

Answer 1

您可以对 out 和 return 的总和进行子选择，然后与您的主生产表 join

select COALESCE(a.qty,0) `out`,p.date, COALESCE(b.qty,0) as ret
from tbl_production p
left join (
  select `date`, sum(qty) qty
  from tbl_out
  group by `date`
) a on(p.date = a.date)
left join (
  select `date`, sum(qty) qty 
  from tbl_return
  group by `date`
) b on(p.date = b.date)

DEMO