MySQL从多个表中选择和求和而不重复

debugcn 发表于 Dev

用户名

我有3张桌子：

table1

code(Primary key) | name | quantity

B001 | sand | 50

B002 | nail | 100

B003 | paint | 10

=======

table2

code | qty_out

B001 | 2

B001 | 1

B001 | 20

B002 | 10

B002 | 30

=======

table3

code  | qty_in

B001   | 1

B001   | 5

B002   | 5

B002  | 10

=======

我想要的结果是：

table1

code  | name    | quantity  | Out      | In      | total

B001  | sand    | 50        | 23       | 6       | 33

B002  | nail    | 100       | 40       | 15     |  75

B003  | paint   | 10        | null/0  |  null/0  | 10

我用这个查询：

SELECT table1.code, table1.name, table1.quantity, sum(table2.qty_out ) AS 'Out', sum( table3.qty_in ) AS 'In'
FROM table1
LEFT JOIN table2 ON table2.code = table1.code
LEFT JOIN table3 ON table3.code = table1.code
GROUP BY table1.code
ORDER BY table1.code ASC

在该查询中，我得到这样的结果...代码B001在46中，在18中，代码B002在80中，在30中，代码B003在null中，在null中

如何解决这个问题？

Ashishmaurya

使用此查询

select t.code,t.name,t.quantity,t.out,t.in,(t.out+t.in) as total
from ( 
      SELECT table1.code, table1.name, table1.quantity,
                ( select sum(table2.qty_out) 
                  from table2 
                  where table1.code=table2.code ) as out,
                ( select sum(table3.qty_in) 
                  from table3
                  where table3.code=table1.code ) as in  
      FROM table1
     ) as t

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。