只有用VBA了,思路:
1、先将所有月份、品种生成临时表,如你所述;
2、用UPDATE将表中已有的数据UPDATE or 用 union;
3、用自连接来求累计。
示例,代码量也差不多:
SELECT A.品种, A.月份, A.sl, A.sl+NZ((SELECT SUM(sl) FROM
(select 品种, 月份, sum(数量) as sl from (sELECT * FROM LJ union
select 1,'铅笔',0 from lj union
select 2,'铅笔',0 from lj union
select 3,'铅笔',0 from lj union
select 4,'铅笔',0 from lj union
select 5,'铅笔',0 from lj union
select 1,'文具盒',0 from lj union
select 2,'文具盒',0 from lj union
select 3,'文具盒',0 from lj union
select 4,'文具盒',0 from lj union
select 5,'文具盒',0 from lj
) group by 品种, 月份
) b
WHERE A.品种=品种 AND A.月份>月份),0) AS LJ
FROM [select 品种, 月份, sum(数量) as sl from (sELECT * FROM LJ union
select 1,'铅笔',0 from lj union
select 2,'铅笔',0 from lj union
select 3,'铅笔',0 from lj union
select 4,'铅笔',0 from lj union
select 5,'铅笔',0 from lj union
select 1,'文具盒',0 from lj union
select 2,'文具盒',0 from lj union
select 3,'文具盒',0 from lj union
select 4,'文具盒',0 from lj union
select 5,'文具盒',0 from lj
) group by 品种, 月份]. AS a;
[此贴子已经被作者于2005-12-8 11:38:46编辑过]
|