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)