create table table1(name char(50),total int )
insert into table1 (name,total) values('A',200);insert into table1 (name,total) values('B',1000);
mysql> select * from table1;+------+-------+| name | total |+------+-------+| A | 200 || B | 1000 |+------+-------+2 rows in set (0.00 sec)
create table table2(name char(50),number int)
insert into table2 (name,number) values('A',20);insert into table2 (name,number) values('A',80);insert into table2 (name,number) values('B',100);insert into table2 (name,number) values('B',200);
mysql> select * from table2;+------+--------+| name | number |+------+--------+| A | 20 || A | 80 || B | 100 || B | 200 |+------+--------+4 rows in set (0.00 sec)
select aa.name,(aa.total-cc.total1)as sub
from table1 aa,(select sum(number)as total1 from table2 group by name) cc
group by name;
查询库存结果为：
+------+------+| name | sub |+------+------+| A | 100 || B | 900 |+------+------+2 rows in set (0.00 sec)
转载于:https://www.cnblogs.com/celine/p/8479790.html
相关资源：库存收发存报表汇总统计数据库语句实现