MYSQL 选择连接多个表并求和

2022-01-09 00:00:00 join sum left-join mysql inner-join

我有三张桌子

1.master

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

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

I must build a query to give result

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超过现场数量，我真的不知道为什么.

i dont know how to combine full list of master, trans_in and trans_out by time_trans (if null then 0), i already try using left join, inner join on three of them but the result make me dizzy.. result from SUM more than qty from field, i really dont know why.

推荐答案

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

you can left join to some derived tables just in case you get multiple entries per date

    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;

相关文章