select a.书ID,a.书名,b.进货价格,b.进货数量,c.出货数量,c.出货价格,isnull(b.进货数量,0)-isnull(c.出货数量,0) as 现库存量,a.类别,a.出版社 from 书库 a left join (select 书ID,sum(数量) as 进货数量,max(进货价格) as 进货价格 from 进货 group by 书ID) b on a.书ID=b.书ID left join (select 书ID,sum(数量) as 出货数量,max(出货价格) as 出货价格 from 出货 group by 书ID) c on a.书ID=c.书ID
Select a.书ID,a.书名,IsNull(b.进货价格,0) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,IsNull(c.出货价格,0) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量, sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量 from 书库表 a Left Join 进货表 b on a.书ID = b.书ID Left Join 出货表 c on a.书ID = c.书ID
select a.书ID,a.书名,isnull(b.进货价格,0) as 进货价格,isnull(b.进货数量,0) as 进货数量,isnull(c.出货数量,0) as 出货数量,isnull(c.出货价格,0) as 出货价格,isnull(b.进货数量,0)-isnull(c.出货数量,0) as 现库存量,a.类别,a.出版社 from 书库 a left join (select 书ID,sum(数量) as 进货数量,max(进货价格) as 进货价格 from 进货 group by 书ID) b on a.书ID=b.书ID left join (select 书ID,sum(数量) as 出货数量,max(出货价格) as 出货价格 from 出货 group by 书ID) c on a.书ID=c.书ID
Select a.书ID,a.书名,IsNull(b.进货价格,0) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,IsNull(c.出货价格,0) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量, sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量 from 书库表 a Left Join 进货表 b on a.书ID = b.书ID Left Join 出货表 c on a.书ID = c.书ID group by a.书ID
--进货价格、出货价格要最大价格、最小价格、还是平均价格? --如果要平均价格 Select a.书ID,a.书名,avg(IsNull(b.进货价格,0)) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,avg(IsNull(c.出货价格,0)) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量, sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量,a.类别,a.出版社 from 书库表 a Left Join 进货表 b on a.书ID = b.书ID Left Join 出货表 c on a.书ID = c.书ID group by a.书ID
to: sdhdy(大江东去...) 1. 进货价格,数量是进货表中相同书ID的汇总,价格可能不相同,取AVG(不需要单价*数量 汇总后再取了,直接汇总单价取就行了) 2.如上
--如果要最大价格 Select a.书ID,a.书名,max(IsNull(b.进货价格,0)) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,max(IsNull(c.出货价格,0)) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量, sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量,a.类别,a.出版社 from 书库表 a Left Join 进货表 b on a.书ID = b.书ID Left Join 出货表 c on a.书ID = c.书ID group by a.书ID
select a.书ID,a.书名,isnull(b.进货价格,0) as 进货价格,isnull(b.进货数量,0) as 进货数量,isnull(c.出货数量,0) as 出货数量,isnull(c.出货价格,0) as 出货价格,isnull(b.进货数量,0)-isnull(c.出货数量,0) as 现库存量,a.类别,a.出版社 from 书库 a left join (select 书ID,sum(数量) as 进货数量,avg(进货价格) as 进货价格 from 进货 group by 书ID) b on a.书ID=b.书ID left join (select 书ID,sum(数量) as 出货数量,avg(出货价格) as 出货价格 from 出货 group by 书ID) c on a.书ID=c.书ID可能的情况: 只有书库信息而没有进货的,如何处理? 我这里都是显示0的
sdhdy(大江东去...) : group by 都错了可能要group by a.书ID,a.书名,a.类别,a.出版社
--如果要最大价格 Select a.书ID,a.书名,max(IsNull(b.进货价格,0)) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,max(IsNull(c.出货价格,0)) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量, sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量,a.类别,a.出版社 from 书库表 a Left Join 进货表 b on a.书ID = b.书ID Left Join 出货表 c on a.书ID = c.书ID group by a.书ID,a.书名,a.类别,a.出版社
楼主说的还是不太明白? --如果要取平均价格 Select a.书ID,a.书名,avg(IsNull(b.进货价格,0)) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,avg(IsNull(c.出货价格,0)) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量, sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量,a.类别,a.出版社 from 书库表 a Left Join 进货表 b on a.书ID = b.书ID Left Join 出货表 c on a.书ID = c.书ID group by a.书ID,a.书名,a.类别,a.出版社
方法一样: 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
用FULL JOIN加GROUP BY就可以了.
TO: Varchar(可变长字符串) 可能的情况: 只有书库信息而没有进货的,如何处理? 我这里都是显示0的此情况不存在,在进货操作时,才操作进货和书库这2个表
Select 书库.书ID,书库.书名,avg(IsNull(进货.进货价格,0)) as 进货价格,sum(IsNull(进货.数量,0)) as 进货数量,avg(IsNull(出货.出货价格,0)) as 出货价格,sum(IsNull(出货.数量,0)) as 出货数量, sum(IsNull(进货.数量,0)) - sum(IsNull(出货.数量,0)) as 现存量,书库.类别,书库.出版社 from 书库 left Join 进货 on 书库.书ID = 进货.书ID left join 出货 on 书库.书ID=出货.书ID group by 书库.书ID,书库.书名,书库.类别,书库.出版社我这样写了后,在ACCESS数据库中查询,好象提示:语法错误,操作符丢失在 left Join 进货 on 书库.书ID = 进货.书ID left join 出货 on 书库.书ID=出货.书ID 中,不知道为什么?
from 书库 a left join (select 书ID,sum(数量) as 进货数量,max(进货价格) as 进货价格 from 进货 group by 书ID) b
on a.书ID=b.书ID
left join (select 书ID,sum(数量) as 出货数量,max(出货价格) as 出货价格 from 出货 group by 书ID) c
on a.书ID=c.书ID
sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量
from 书库表 a
Left Join 进货表 b on a.书ID = b.书ID
Left Join 出货表 c on a.书ID = c.书ID
1、进货价格,进货数量是进货表中相同书ID的汇总数吗?相同书ID的进货价格每次都相同吗?不同的进货价格如何处理?
2、货数量,出货价格同上
from 书库 a left join (select 书ID,sum(数量) as 进货数量,max(进货价格) as 进货价格 from 进货 group by 书ID) b
on a.书ID=b.书ID
left join (select 书ID,sum(数量) as 出货数量,max(出货价格) as 出货价格 from 出货 group by 书ID) c
on a.书ID=c.书ID
sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量
from 书库表 a
Left Join 进货表 b on a.书ID = b.书ID
Left Join 出货表 c on a.书ID = c.书ID
group by a.书ID
1、进货价格,进货数量是进货表中相同书ID的汇总数吗?相同书ID的进货价格每次都相同吗?不同的进货价格如何处理?
2、出货数量,出货价格同上
--如果要平均价格
Select a.书ID,a.书名,avg(IsNull(b.进货价格,0)) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,avg(IsNull(c.出货价格,0)) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量,
sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量,a.类别,a.出版社
from 书库表 a
Left Join 进货表 b on a.书ID = b.书ID
Left Join 出货表 c on a.书ID = c.书ID
group by a.书ID
1. 进货价格,数量是进货表中相同书ID的汇总,价格可能不相同,取AVG(不需要单价*数量 汇总后再取了,直接汇总单价取就行了)
2.如上
Select a.书ID,a.书名,max(IsNull(b.进货价格,0)) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,max(IsNull(c.出货价格,0)) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量,
sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量,a.类别,a.出版社
from 书库表 a
Left Join 进货表 b on a.书ID = b.书ID
Left Join 出货表 c on a.书ID = c.书ID
group by a.书ID
from 书库 a left join (select 书ID,sum(数量) as 进货数量,avg(进货价格) as 进货价格 from 进货 group by 书ID) b
on a.书ID=b.书ID
left join (select 书ID,sum(数量) as 出货数量,avg(出货价格) as 出货价格 from 出货 group by 书ID) c
on a.书ID=c.书ID可能的情况:
只有书库信息而没有进货的,如何处理?
我这里都是显示0的
group by 都错了可能要group by a.书ID,a.书名,a.类别,a.出版社
Select a.书ID,a.书名,max(IsNull(b.进货价格,0)) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,max(IsNull(c.出货价格,0)) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量,
sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量,a.类别,a.出版社
from 书库表 a
Left Join 进货表 b on a.书ID = b.书ID
Left Join 出货表 c on a.书ID = c.书ID
group by a.书ID,a.书名,a.类别,a.出版社
--如果要取平均价格
Select a.书ID,a.书名,avg(IsNull(b.进货价格,0)) as 进货价格,sum(IsNull(b.数量,0)) as 进货数量,avg(IsNull(c.出货价格,0)) as 出货价格,sum(IsNull(c.数量,0)) as 出货数量,
sum(IsNull(b.数量,0)) - sum(IsNull(c.数量,0)) as 现存量,a.类别,a.出版社
from 书库表 a
Left Join 进货表 b on a.书ID = b.书ID
Left Join 出货表 c on a.书ID = c.书ID
group by a.书ID,a.书名,a.类别,a.出版社
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
只有书库信息而没有进货的,如何处理?
我这里都是显示0的此情况不存在,在进货操作时,才操作进货和书库这2个表
sum(IsNull(进货.数量,0)) - sum(IsNull(出货.数量,0)) as 现存量,书库.类别,书库.出版社
from 书库
left Join 进货 on 书库.书ID = 进货.书ID
left join 出货 on 书库.书ID=出货.书ID
group by 书库.书ID,书库.书名,书库.类别,书库.出版社我这样写了后,在ACCESS数据库中查询,好象提示:语法错误,操作符丢失在 left Join 进货 on 书库.书ID = 进货.书ID left join 出货 on 书库.书ID=出货.书ID 中,不知道为什么?