Join 3 Table Sum Group By MySQL PHP

Question

我正在使用Codeigniter，我想要sum(stock_in)和sum(stock_out)和group by id_barang:

股票表

这是一种方法(使用RahulBiswas提供的小提琴)…

SELECT s.code   
, s.name
, s.stock
, SUM(CASE WHEN direction = 'in' THEN qty END) stock_in
, SUM(CASE WHEN direction = 'out' THEN qty END) stock_out
, s.stock+SUM(CASE WHEN direction = 'in' THEN qty END)-SUM(CASE WHEN direction = 'out' THEN qty END) balance
FROM stock s
JOIN 
( SELECT code
, name
, stock_in qty
, 'in' direction
FROM stock_in
UNION ALL
SELECT code
, name
, stock_out 
, 'out' 
FROM stock_out
) x
ON x.code = s.code;

http://sqlfiddle.com/!9/6dc765/10

根据您想要的输出，这里使用INNER JOIN。但是，如果代码(B01)不存在于stock_in和Stock_out表中，则使用LEFT JOIN而不是INNER JOIN。在金额字段中使用COALESCE函数来忽略NULL。

-- MySQL
SELECT s.code, s.name, s.stock, si.stock_in, so.stock_out
, (s.stock + si.stock_in - so.stock_out) last_stock
FROM Stock s
INNER JOIN (SELECT code, MAX(name) name
, SUM(stock_in) stock_in
FROM Stock_in
GROUP BY code) si
ON s.code = si.code
INNER JOIN (SELECT code, MAX(name) name
, SUM(stock_out) stock_out
FROM Stock_out             
GROUP BY code) so
ON s.code = so.code;

请检查url http://sqlfiddle.com/#的结果!9/6dc765/4

请检查使用左连接的结果http://sqlfiddle.com/#!9/9e97ef3/2