500分求高效算法（另开贴结分），有关数据库的数据汇总 之二

[mylzw]

首先~ 欢迎大家的参与。

现在要汇总一个表中时间段内的数据，数据库为ACCESS2000格式。
表结构是这样的。

单据编号,货品编码，产品进仓，产品销售，产品退货，单据日期
一律为 Char类型 ；）

现在的目的是，指定一个时间段，比如：2003-01-01 至 2003-01-31 之内的数据。得出以下结果

货品编码，上期结存，产品进仓，产品销售，产品退货，本期结存
JL0202 10 100 50 0 60
Rl805 30 100 50 0 80
............


首先要查询出自第一笔业务(第一笔业务假设为2002-01-01发生的) 以来至2002-12-31的结存作为上期结存。
我是这样做的。
Rs_1=Conn.Execute("select sum(产品进仓) - sum(产品销售) - sum(产品退货) as 上期结存 from 单据 where 单据日期>='2002-01-01' and 单据日期<'2003-01-01' Group By 货品编码")

这样就得到了所有货品的上期结存.
然后。。。
Rs_2=Conn.Execute("select sum(产品进仓),sum(产品销售),sum(产品退货) as 本期结存 from 单据 where 单据日期>='2003-01-01' and 单据日期<='2003-01-31' Group By 货品编码")

这样就得到了所有货品的本期汇总数据。
现在的问题是，要逐个货品的将刚刚得到 上期结存 + 本期结存 才能得出真正本期结存。

我现在是用了两个临时表，一个存储 Rs_1 , 一个存储 Rs_2
然后 "select Rs_1.上期结存 + Rs_2.本期结存 as 本期结存_val from Rs_1 Full join Rs_2 on Rs_1.货品编码 = Rs_2.货品编码" '这样将得到真正的本期结存

我想知道，除了这种方法，还有没有别的好办法呢？

(建立临时表的时候用了 DO until Rs_1.eof 及 insert 查询。这样当记录较多的情况下会影响速度。。。)

[mylzw]

另外，还有一个表 QCKC 存放着货品的期初库存数据。 当我按上述方法汇总完毕后，要将最后的本期结存 + 期初库存，才是本月的真实库存。
目前我用的是个笨方法，将刚刚汇总的结果保存为一个表，然后打开这两个表为Rs、Rs_q
Do Until Rs.Eof
Rs_q.open "select * from QCKC where 货品编码='" & Rs.Fields("货品编码") & "'",conn, adOpenKeyset, adLockOptimistic

Rs.Fields("本期结存")=Rs.Fields("本期结存") + Rs_q("期初库存")
Rs.Update
Rs.MoveNext
Rs_q.close
loop

不知道有没有更好的办法。

[golden24kcn]

update table a set 本期结存=(select sum(产品进仓) - sum(产品销售) - sum(产品退货) as 上期结存 from 单据 where 单据日期>='2002-01-01' and 单据日期<'2003-01-01' where 货品编码=a.货品编码 Group By 货品编码)

这样不行吗?

[mylzw]

golden24kcn(不甜) 你的语句俺不懂啊。

[DreamManor]

golden24kcn(不甜) 的语句是TSQL语句的语法，在SQL Server中是可行的，
但Access中使用的是MSQL语法。有些TSQL语句的语法是行不通的。

[kinlin]

SELECT 货品编码,sum(iif(left(单据日期,4)=2002,产品表.产品进仓-产品表.产品销售-产品退货,0))+sum(iif(left(单据日期,4)=2003,产品退货,0)) AS 本期结存
FROM 产品表
WHERE 单据日期>='2002-01-01' and 单据日期<='2003-01-31'
group by 货品编码

[xiaozikuge888]

up

[mylzw]

up

[yoki]

哪个地方不懂？

[奋斗一生的程序员]

芃~ 抱歉，我怕是帮不了你这个忙。越看越乱。
其实对于联合查询我们可以用更简洁的语法。

Select A.*,B.* From A
Inner Join B
On A.编号=B.编号 '联接表A和B的条件
Where.......

这才可以真正做到一步到位。

[mylzw]

to yoki(小马哥)
这是一条查询吗?
) t1, 这段是什么意思啊？

[yoki]

一步到位：
select t1.货品编码,产品进仓,产品销售,产品退货,(t3.期初库存+t1.本期结存-t2.上期结存) as 真实库存
from
(
select 货品编码,sum(产品进仓) as 产品进仓,sum(产品销售) as 产品销售,sum(产品退货) as 产品退货,
sum(产品进仓)-sum(产品销售)-sum(产品退货) as 本期结存
from 单据
where 单据日期 between '2003-01-01' and '2003-01-31'
group by 货品编码
) t1,
(
select sum(产品进仓)-sum(产品销售)-sum(产品退货) as 上期结存
from 单据
where 单据日期<'2003-01-01'
group by 货品编码
) t2 ,QCKC as t3
where t1.货品编码=t2.货品编码 and t1.货品编码=t3.货品编码

另外：注意修改你的数据类型，怎么能一律为 Char类型呢？这样对统计有很大的影响，数值类型比字符类型快的多

[mylzw]

谢谢小马哥~ 俺得好好研究研究你的语句！ 学习 ……

[yoki]

(
select 货品编码,sum(产品进仓) as 产品进仓,sum(产品销售) as 产品销售,sum(产品退货) as 产品退货,
sum(产品进仓)-sum(产品销售)-sum(产品退货) as 本期结存
from 单据
where 单据日期 between '2003-01-01' and '2003-01-31'
group by 货品编码
) t1,
这一段是构造一个查询，使查询记录集构成一个别名为t1的表，其实跟临时表差不多，建议你看一看联合查询以及子查询方面的书

[vbman2003]

我是向各位学习来的。看了你的程序，觉得你运算太慢的问题，可能并不是找出一个更好的计算方法就能完全解决的。应该从你的数据库结构上来解决问题。建议更改一下你数据库结构：
1.用户表：包含用户ID、密码、姓名等信息。
2.物资表：包含物资ID、名称、规格、型号。
3.入库表：包含物资的基本信息、及入库的相关信息。建议不能全部用char类型，特别是要计算的字段，否则会制约运算速度。另外，datetime型字段也不不要用char类型，否则查询中也有可能出现莫名错误。
4.出库表：同3
5.库存表：包含物资的基本信息、及库存数量、金额、仓库等信息。
有一个合理的数据库逻辑结构，再寻求一个合理的计算方法，才是解决你问题的关键。一孔之见，敬请各位高手指正。

[yoki]

确实，你的表结构设计不太合理，给一个例子给你参考一下：
--下面的仅供参考:

--数据测试环境
create table 物料明细表(物料ID varchar(6),名称 varchar(10),规格 varchar(10),颜色 varchar(10),单位 varchar(10),所属类别 varchar(10))
insert into 物料明细表
select 'A-001','皮纸','20×30×50','红','张','纸'
union all select 'B-002','磁铁','20×10×50','','块','五金'

create table 进仓主表 (进仓ID varchar(20),供应商ID varchar(10),进仓日期 datetime,原凭证号 varchar(10),入库类型 int)
insert into 进仓主表
select 'J-20031015-001','SA001','2003-10-15','123456',1
union all select 'J-20031015-002','SA002','2003-10-15','123457',1
union all select 'J-20031017-001','SA002','2003-10-17','123456',2

create table 进仓明细表 (进仓ID varchar(20),物料ID varchar(6),数量 decimal(20,2))
insert into 进仓明细表
select 'J-20031015-001','A-001',200
union all select 'J-20031015-002','B-002',99
union all select 'J-20031017-001','A-001',150.5

create table 出仓主表 (出仓ID varchar(20),部门ID varchar(10),领料日期 datetime,领料单号 varchar(10),出库类型 varchar(1))
insert into 出仓主表
select 'C-20031015-001','Dept_P','2003-10-15','1111111','P'
union all select 'C-20031016-001','Dept_P','2003-10-16','1111112','P'

create table 出仓明细表 (出仓ID varchar(20),物料ID varchar(6),数量 decimal(20,2))
insert into 出仓明细表
select 'C-20031015-001','A-001',50
union all select 'C-20031016-001','A-001',50


go

--为数据处理添加视图
create view qry进仓单
as
select b.物料ID,a.进仓日期,进仓数量=sum(b.数量)
from 进仓主表 a join 进仓明细表 b on a.进仓ID=b.进仓ID
group by b.物料ID,a.进仓日期

go
create view qry出仓单
as
select b.物料ID,出仓日期=a.领料日期,出仓数量=sum(b.数量)
from 出仓主表 a join 出仓明细表 b on a.出仓ID=b.出仓ID
group by b.物料ID,a.领料日期

go
create view qry进出记录
as
select 物料ID=isnull(a.物料ID,b.物料ID)
,a.进仓日期,进仓数量=sum(a.进仓数量)
,b.出仓日期,出仓数量=sum(b.出仓数量)
,日期=isnull(a.进仓日期,b.出仓日期)
from qry进仓单 a full join qry出仓单 b
on a.物料ID=b.物料ID and a.进仓日期=b.出仓日期
group by isnull(a.物料ID,b.物料ID),a.进仓日期,b.出仓日期,isnull(a.进仓日期,b.出仓日期)


go

--进出记录表
select a.*
,进仓日期=convert(varchar(10),b.进仓日期,120),b.进仓数量
,出仓日期=convert(varchar(10),b.出仓日期,120),b.出仓数量
,当前库存=isnull((select sum(进仓数量) from qry进仓单 where 物料ID=a.物料ID and 进仓日期<=b.日期),0)
-isnull((select sum(出仓数量) from qry出仓单 where 物料ID=a.物料ID and 出仓日期<=b.日期),0)
from 物料明细表 a left join qry进出记录 b on a.物料ID=b.物料ID
order by a.物料ID,b.日期

--当前库存表
select a.*,b.当前库存
from 物料明细表 a left join (
select 物料ID,当前库存=isnull(sum(进仓数量),0)-isnull(sum(出仓数量),0) from qry进出记录 group by 物料ID
)b on a.物料ID=b.物料ID
order by a.物料ID

[mylzw]

谢谢大家 ：）

[vikingleo]

mark