Access数据库中结构是这样的:
ID Time XX
1 1999-12-1 1
2 2000-12-1 3
3 2001-12-1 8
4 2001-3-1 9
5 2001-6-1 13
6 2002-12-1 18
.. ......... ...现在要计算相邻两年中对应的XX之和,例如1999-12-1的XX加上2000-12-1的XX,
2000-12-1的XX加上2001-12-1的XX
2001-12-1的XX 加2002-12-1的XX,依此类推..............
ID Time XX
1 1999-12-1 1
2 2000-12-1 3
3 2001-12-1 8
4 2001-3-1 9
5 2001-6-1 13
6 2002-12-1 18
.. ......... ...现在要计算相邻两年中对应的XX之和,例如1999-12-1的XX加上2000-12-1的XX,
2000-12-1的XX加上2001-12-1的XX
2001-12-1的XX 加2002-12-1的XX,依此类推..............
然后做一次循环,读取2年内的xx即可。access可以使用DateDiff
where a.id=b.id-1
select a.xx+b.xx from tablename a,tablename b
where a.time=DateAdd(year,1,b.time)
where convert(varchar(4),a.Time,121) = convert(varchar(4),b.Time,121) -1
这是查询一年内的总和.
1 1999 *
2 2000 *
3 2001 *试试这样行不行.select sum(xx) from table a left join table b on
month(b.time)<12 and year(a.time)-year(b.time)=1
where a.time=DateAdd(year,1,b.time)
---------------------------------------
你的意思是要建两个表吗?我现在只在一个表下要做这样的计算
同时需要对所有的数据进行计算,还要选出不是年份的那些,如:
4 2001-3-1 9
5 2001-6-1 13
另,什么叫“不是年份的那些”?
SELECT a.Time ,b.Time,a.xx+b.xx
FROM Table1 as a,Table1 as b
WHERE DateDiff(year,b.Time,a.Time=1) and DatePart(month,a.Time)=12
a和b的意思不是要建两张表,而是同一张表,用a和b作为它的别称
select a.time,a.xx+isnull(b.xx,0)
from table1 a
left outer join table1 b on datediff(mm,b.time,a.time)=12
FROM Table1 as a,Table1 as b
WHERE DateDiff(year,b.Time,a.Time=1) and DatePart(month,a.Time)=12这个想法不错,我现在数据表名是Info,就这样:
SELECT a.Time ,b.Time,a.xx+b.xx
FROM Info as a,Info as b
WHERE DateDiff(year,b.Time,a.Time=1) and DatePart(month,a.Time)=12
对不对?