ACCESS数据库,在进出库系统中对仓库进行盘点,盘点后使用SQL语句对库存进行统计的问题

[Quinten]

有三个表,入库表,出库表和盘点表

入库表
[货品名称] [入库数量] [入库时间]
鼠标 55 2009-01-01
键盘 22 2009-01-01
鼠标 2 2009-01-01

出库表
[货品名称] [出库数量] [出库时间]
鼠标 15 2009-01-02
鼠标 7 2009-01-23

盘点表
[货品名称] [实盘数量] [盘点时间]
鼠标 39 2009-01-04
鼠标 38 2009-01-05

现在想知道怎样才能得到库存表:

库存表
[货品名称] [库存数量] [统计时间]
鼠标 31 2009-03-06
键盘 22 2009-03-06

计算方法:
因为库存表的统计时间(2009-03-06)大于上述的入库时间、出库时间和盘点时间，"鼠标"的第二次盘点时间(2009-01-05)>第一次盘点时间(2009-01-04)又大于"鼠标"的两次入库时间(2009-01-01),但小于第二次"鼠标"出库时间

(2009-01-23)，所以：
"鼠标" 2009-03-06时的库存量为38-7=31件
"键盘" 2009-03-06时的库存量为22件.
也就是说,对于盘点时间前的货物的进出库数量都可以忽略了,库存数量应该等于: 盘点数量-盘点时间后的出库数量之和+盘点时间后的进库数量之和
这库存表的SQL语句怎么写?
如果对货物都进行了盘点,SQL语句还好写,但如果只对有些货物进行了盘点,而有些货物没有进行盘点,SQL语句我绞尽脑汁也没写出来.哪位大哥会啊,谢谢!

[WWWWA]

try:
SELECT a.货品名称,a.实盘数量-nz(b.出库数量,0)+nz(c.入库数量,0) as 库存数量,date() as 统计时间 from (盘点表 a
left join 出库表 b on a.货品名称=b.货品名称 and a.盘点时间<b.出库时间)
left join 入库表 c on a.货品名称=c.货品名称 and a.盘点时间<c.入库时间
where a.盘点时间=(select max(盘点时间) from 盘点表 group by 货品名称)
union
SELECT a.货品名称,a.入库数量,date() from 入库表 a left join 盘点表 b
on a.货品名称=b.货品名称
where b.货品名称 is null

[WWWWA]

try:
SELECT a.货品名称,a.实盘数量-nz(b.出库数量,0)+nz(c.入库数量,0) as 库存数量,date() as 统计时间 from ((盘点表 a
left join 出库表 b on a.货品名称=b.货品名称 and a.盘点时间 <b.出库时间)
left join 入库表 c on a.货品名称=c.货品名称 and a.盘点时间 <c.入库时间)
inner join (select 货品名称,max(盘点时间) as ma from 盘点表 group by 货品名称) d
on a.盘点时间=d.ma and a.货品名称=d.货品名称
union
SELECT a.货品名称,a.入库数量,date() from 入库表 a left join 盘点表 b
on a.货品名称=b.货品名称
where b.货品名称 is null

[Quinten]

谢谢楼上大哥,实验可以通过!
但是最后(UNION)后面的语句有个问题,如果"键盘"有出库数据(同时也没有盘点数据)呢?那样的话UNION 后面的语句应该怎么写?谢谢!

[WWWWA]

在写SQL语句时考虑过这个问题，即在表中没有完整数据的情况下应该怎样处理，
简单地讲，从
SELECT 货品名称 FROM 入库表
UNION
SELECT 货品名称 FROM 出库表
UNION
SELECT 货品名称 FROM 盘点表

取得完整的货品名称，再与其它表连接，你可以自行测试一下，

[Quinten]

我用下面的SQL语句完成的,但是感觉是不是笨了点?有没有更好的方法?谢谢!

SELECT e.货品编码, SUM(e.库存数量) AS 数量
FROM (SELECT a.货品编码,a.实盘数量-nz(b.出库数量,0)+nz(c.入库数量,0) as 库存数量 from ((盘点单表 a
left join 出库单表 b on a.货品编码=b.货品编码 and a.盘点时间 <b.出库时间)
left join 入库单表 c on a.货品编码=c.货品编码 and a.盘点时间 <c.入库时间)
inner join (select 货品编码,max(盘点时间) as ma from 盘点单表 GROUP BY 货品编码) d
on a.盘点时间=d.ma and a.货品编码=d.货品编码
union
SELECT a.货品编码,a.入库数量 as 库存数量 from 入库单表 a left join 盘点单表 b
on a.货品编码=b.货品编码
where b.货品编码 is null
union
SELECT a.货品编码,-a.出库数量 AS 库存数量 from 出库单表 a left join 盘点单表 b
on a.货品编码=b.货品编码
where b.货品编码 is null) AS e
GROUP BY e.货品编码

[WWWWA]

呵呵，连接应该是最快的方法，只要能解决问题就行，你的要求比较复杂

[Quinten]

那连接应该怎么写呢?不好意思,有点笨.能否提示一下?

[WWWWA]

就是你的查询，呵呵

[Quinten]

谢谢~!结贴!