select 书ID,书名,
isnull((select sum(数量*进货价格)/sum(数量) from 进货 where 书ID = A.书ID and 数量 > 0),0) 进货价格,
isnull((select sum(数量) from 进货 where 书ID = A.书ID),0) 进货数量,
isnull((select sum(数量) from 出货 where 书ID = A.书ID),0) 出货数量,
isnull((select sum(数量*出货价格)/sum(数量) from 出货 where 书ID = A.书ID and 数量 > 0),0) 出货价格,
(isnull((select sum(数量) from 进货 where 书ID = A.书ID),0)
- isnull((select sum(数量) from 出货 where 书ID = A.书ID),0)) 现库存量,
类别,出版社
from 书库 A
isnull((select sum(数量*进货价格)/sum(数量) from 进货 where 书ID = A.书ID and 数量 > 0),0) 进货价格,
isnull((select sum(数量) from 进货 where 书ID = A.书ID),0) 进货数量,
isnull((select sum(数量) from 出货 where 书ID = A.书ID),0) 出货数量,
isnull((select sum(数量*出货价格)/sum(数量) from 出货 where 书ID = A.书ID and 数量 > 0),0) 出货价格,
(isnull((select sum(数量) from 进货 where 书ID = A.书ID),0)
- isnull((select sum(数量) from 出货 where 书ID = A.书ID),0)) 现库存量,
类别,出版社
from 书库 A
解决方案 »
- print函数问题,求详解
- 请问存储过程怎么查询前@n条数据(select top @n * from……报错
- 搞了好一阵子,写了个触发器,测试的时候每次都只UPDATE一条记录。发现没有问题,但。。。。
- ^^^^^^^^^^^^^^见鬼,提问时打错了字
- 一个排序的问题,请大家帮助
- 有没有什么小工具或小软件能格式化SQL?
- sqlserver2000中两个数据库如何保持同步?
- mysql 问题,,,百分求解
- 怎样按时间段分组?
- 本论坛最简单的问题在这里~~
- 自己创建的表,不能访问,在线等待
- 求一不算太复杂的SQL语言书写(SELECT),关于置顶问题,并将送一组50M的SQL Server数据库空间(天互商务自带)给这位老鸟...
isnull((select sum(数量*进货价格)/sum(数量) from 进货 where 书ID = A.书ID and 数量 > 0),0) 进货价格,
isnull((select sum(数量) from 进货 where 书ID = A.书ID),0) 进货数量,
isnull((select sum(数量) from 出货 where 书ID = A.书ID),0) 出货数量,
isnull((select sum(数量*出货价格)/sum(数量) from 出货 where 书ID = A.书ID and 数量 > 0),0) 出货价格,
(isnull((select sum(数量) from 进货 where 书ID = A.书ID),0)
- isnull((select sum(数量) from 出货 where 书ID = A.书ID),0)) 现库存量,
类别,出版社
from 书库 A
isnull((select sum(数量*进货价格)/sum(数量) from 进货 where 书ID = A.书ID and 数量 > 0),0) 进货价格,
isnull((select sum(数量) from 进货 where 书ID = A.书ID),0) 进货数量,
isnull((select sum(数量) from 出货 where 书ID = A.书ID),0) 出货数量,
isnull((select sum(数量*出货价格)/sum(数量) from 出货 where 书ID = A.书ID and 数量 > 0),0) 出货价格,
(isnull((select sum(数量) from 进货 where 书ID = A.书ID),0)
- isnull((select sum(数量) from 出货 where 书ID = A.书ID),0)) 现库存量,
类别,出版社
from 书库 A
Nz(B.进货数量,0) - Nz(C.出货数量,0) 现存量,A.类别,A.出版社
from 书库 A LEFT JOIN
(select 书ID,SUM(数量) AS 进货数量,avg(nz(b.进货价格,0)) AS 进货价格
FROM 进货 GROUP BY 书ID) B ON B.书ID = A.书ID LEFT JOIN
(select 书ID,SUM(数量) AS 出货数量,avg(nz(b.出货价格,0)) AS 出货价格
FROM 出货 GROUP BY 书ID) C ON C.书ID = A.书ID
NZ(B.进货数量,0),Nz(C.出货价格,0),NZ(C.出货数量,0),
Nz(B.进货数量,0) - Nz(C.出货数量,0) 现存量,
from 书库 A LEFT JOIN
(select 书ID,SUM(数量) AS 进货数量,avg(nz(b.进货价格,0)) AS 进货价格
FROM 进货 GROUP BY 书ID) B ON B.书ID = A.书ID LEFT JOIN
(select 书ID,SUM(数量) AS 出货数量,avg(nz(b.出货价格,0)) AS 出货价格
FROM 出货 GROUP BY 书ID) C ON C.书ID = A.书ID