MYSQL选择联接多个表和SUM [英] MYSQL select join multiple table and SUM
本文介绍了MYSQL选择联接多个表和SUM的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有三个桌子
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.trans_in
2. trans_in
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.trans_out
3. trans_out
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组合主,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;
这篇关于MYSQL选择联接多个表和SUM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文