MYSQL select join multiple table and SUM

Question

我有三个表

1.大师

id_master barcode name last_stock 
1         123     abc  15 
2         234     bcd  10 
3         345     cde  7 
4         456     def  11 
5         567     efg  20

2。转入

id_trans_in barcode qty time_trans 
1           567     1   2016-04-01 10:15:09
2           234     2   2016-04-01 14:15:09
3           345     5   2016-04-01 20:15:09

3。转出

id_trans_out barcode qty time_trans 
1           123     3   2016-04-01 09:15:09
2           234     5   2016-04-01 08:15:09

我必须建立一个查询来给出结果

id_master barcode name last_stock in out
1         123     abc  15         0   3
2         234     bcd  10         2   5
3         345     cde  7          5   0
4         456     def  11         0   0
5         567     efg  20         1   0

我只是尝试简单的查询

select
    a.id_master, a.barcode, a.name,
    COALESCE(SUM(b.qty),'0')
from
    master a
inner join trans_in b
on a.barcode = b.barcode
where
    date(b.time_trans) = '2016-04-01'   
group by a.barcode

结果是

id_master barcode name COALESCE(SUM(b.qty),'0')
5         567     efg  1
2         234     bcd  2
3         345     cde  5

我不知道如何通过 time_trans 组合 master、trans_in 和 trans_out 的完整列表(如果为 null 则为 0)，我已经尝试在其中三个上使用左连接、内连接但结果让我头晕.. SUM 的结果超过 qty 来自现场，我真的不知道为什么。

Answer 1

您可以左连接到一些派生表，以防每个日期有多个条目

    SELECT  a.id_master ,
        a.barcode ,
        a.name ,
        a.last_stock ,
        COALESCE(b.`IN`, 0) AS `IN`,
        COALESCE(c.`OUT`, 0) AS `OUT`
FROM    master a
        LEFT JOIN (SELECT barcode, SUM(qty) as `IN` 
                     FROM trans_in 
                     WHERE date(time_trans) = '2016-04-01' 
                     GROUP BY barcode ) b ON a.barcode = b.barcode
        LEFT JOIN (SELECT barcode, SUM(qty) as `OUT`
                     FROM trans_out 
                     WHERE date(time_trans) = '2016-04-01' 
                     GROUP BY barcode ) c  ON a.barcode = c.barcode;