select (a.number-b.number) as number from sales a,stock b
select A.进货总数, B.销售总数, (A.进货总数 - B.销售总数) as 库存总数 from 进货表 A, 销售表 B where A.货物名称 = B.货物名称
select Sum(a.进货数量) as 进货总数,Sum(b.销售数量) as 销售总数,(Sum(a.进货数量)-Sum(b.销售数量)) as 库存总数 from a,b where ....
select A.进货总数, B.销售总数, (A.进货总数 - B.销售总数) as 库存总数 from 进货表 A, 销售表 B where A.货物id = B.货物id
select Sum(进货表.进货数量) as 进货总数,Sum(销售表.销售数量) as 销售总数,(Sum(进货表.进货数量)-Sum(销售表.销售数量)) as 库存总数 from 进货表,销售表 where 进货表.货物品种=销售表.货物品种
select [a].进货总数, [b].销售总数, ([a].进货总数 - [b].销售总数) as 库存总数 from 进货表 a, 销售表 b where [a].货物名称 = [b].货物名称
如果是sqlserver 以下测试通过 SELECT (SELECT SUM(col_进货数量) FROM table_进货) AS 进货总数, (SELECT SUM(col_销售数量) FROM table_销售) AS 销售总数, (SELECT SUM(col_进货数量) FROM table_进货) - (SELECT SUM(col_销售数量) FROM table_销售) AS 销售总数, (SELECT SUM(col_进货数量) AS 库存数量 如果你只需要库存数量,请把前两个字段去掉即可 建议用存储过程实现(如果数据量大这样会增加你的速度,我想我写的sql会进行行多次求和,而存储过程可以声明变量,记下销售总数和进货总数,无需重新求和) 提醒,对于你的情况,我认为一旦写成表的联合,必错无疑
select A.进货总数, B.销售总数, (A.进货总数 - B.销售总数) as 库存总数 from 进货表 A, 销售表 B where A.货物id = B.货物id 用id好一些。
gamaster(a明-非sql相关不回):我用是ACCESS,你后面 (SELECT SUM(col_进货数量) FROM table_进货) - (SELECT SUM(col_销售数量) FROM table_销售) AS 销售总数, (SELECT SUM(col_进货数量) AS 库存数量,这一部分有问题吗
试上一试!select 货物品种, sum(数量) as 库存数量 from ( select 货物品种 as 货物品种,进货数量 as 数量 from 进货表 union all select 货物品种,0-销售数量 from 销售表 ) t group by 货物品种
from 进货表 A, 销售表 B
where A.货物名称 = B.货物名称
from 进货表 A, 销售表 B
where A.货物id = B.货物id
from 进货表 a, 销售表 b
where [a].货物名称 = [b].货物名称
SELECT (SELECT SUM(col_进货数量)
FROM table_进货) AS 进货总数, (SELECT SUM(col_销售数量)
FROM table_销售) AS 销售总数, (SELECT SUM(col_进货数量)
FROM table_进货) -
(SELECT SUM(col_销售数量)
FROM table_销售) AS 销售总数,
(SELECT SUM(col_进货数量) AS 库存数量
如果你只需要库存数量,请把前两个字段去掉即可
建议用存储过程实现(如果数据量大这样会增加你的速度,我想我写的sql会进行行多次求和,而存储过程可以声明变量,记下销售总数和进货总数,无需重新求和)
提醒,对于你的情况,我认为一旦写成表的联合,必错无疑
from 进货表 A, 销售表 B
where A.货物id = B.货物id
用id好一些。
FROM table_进货) -
(SELECT SUM(col_销售数量)
FROM table_销售) AS 销售总数,
(SELECT SUM(col_进货数量) AS 库存数量,这一部分有问题吗
from (
select 货物品种 as 货物品种,进货数量 as 数量 from 进货表
union all
select 货物品种,0-销售数量 from 销售表 ) t
group by 货物品种
from 进货表 a, 出货表 b
where a.货物=b.货物