mysql> select*from b;
+------+
| 数量 |
+------+
| 3 |
| 2 |
| 4 |
+------+
3 rows in set (0.02 sec)mysql> select *from c;
+------+------+
| 名称 | 数量 |
+------+------+
| F | 2 |
| F | 1 |
| F | 4 |
+------+------+
3 rows in set (0.03 sec)mysql> select ( (sum(b.数量)) - (sum(c.数量)) ) as 库存 from b,c;
+------+
| 库存 |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
+------+
| 数量 |
+------+
| 3 |
| 2 |
| 4 |
+------+
3 rows in set (0.02 sec)mysql> select *from c;
+------+------+
| 名称 | 数量 |
+------+------+
| F | 2 |
| F | 1 |
| F | 4 |
+------+------+
3 rows in set (0.03 sec)mysql> select ( (sum(b.数量)) - (sum(c.数量)) ) as 库存 from b,c;
+------+
| 库存 |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
-- 楼主的逻辑思路不对,请参考以下sql:drop table if exists test.b ;
drop table if exists test.c ;
use test;
create table test.b select 3 as c union select 2 union select 4;
create table test.c select 2 as c union select 1 union select 4;
select a1.num-a2.num
from( select sum(b.c) num from b) a1, (select sum(c.c) num from c) a2;-- 查询结果出来 是 2
+------+------+------+
| 数量 | 名称 | 数量 |
+------+------+------+
| 3 | F | 2 |
| 2 | F | 2 |
| 4 | F | 2 |
| 3 | F | 1 |
| 2 | F | 1 |
| 4 | F | 1 |
| 3 | F | 4 |
| 2 | F | 4 |
| 4 | F | 4 |
+------+------+------+
9 rows in set (0.00 sec)
+------+------+------+
| 数量 | 名称 | 数量 |
+------+------+------+
| 3 | F | 2 |
| 2 | F | 2 |
| 4 | F | 2 |
| 3 | F | 1 |
| 2 | F | 1 |
| 4 | F | 1 |
| 3 | F | 4 |
| 2 | F | 4 |
| 4 | F | 4 |
+------+------+------+
9 rows in set (0.00 sec)
b_sum
c_sum
这俩是什么
基于这个结果,你计算一下 sum(b.数量) 是多少,如果不明白,请贴出你的人工计算结果,如果你人工计算的结果不是27 则请说明你是如何计算的。
+------+------+------+
| 数量1 | 名称 | 数量2 |
+------+------+------+
| 3 | F | 2 |
| 2 | F | 2 |
| 4 | F | 2 |
| 3 | F | 1 |
| 2 | F | 1 |
| 4 | F | 1 |
| 3 | F | 4 |
| 2 | F | 4 |
| 4 | F | 4 |
+------+------+------+如果上面这个已经结果,让你算出 sum(数量1)都不会,那你需要学习的是小学二年级数学了。 建议在GOOGLE中搜索小学教学网论坛 然后提问。
+------+
| X |
+------+
| X1 |
| X2 |
| X3 |
+------+
select * from c;
+------+
| Y |
+------+
| Y1 |
| Y2 |
| Y3 |
+------+那么,select * from b,c 就是+------+------+
| X | Y |
+------+------+
| X1 | Y1 |
| X1 | Y2 |
| X1 | Y3 |
| X2 | Y1 |
| X2 | Y2 |
| X2 | Y3 |
| X3 | Y1 |
| X3 | Y2 |
| X3 | Y3 |
+------+------+
select ( (sum(b.数量)) - (sum(c.数量)) ) as 库存 from b,c where b.id=c.id;