分组求和的问题
access表R, 结构（日期 站点 R2020），字段都为文本，
日期 站点 R2020
20000101 长沙 0001
20000102 长沙 0121
20000103 长沙 0131
20000104 长沙 0351
20000101 湘潭 0021
20000102 湘潭 0231
20000103 湘潭 0121
... ... ...

要求出一个站点每年每月的和，如下：
年份 1月 2月 ... 年

我用：
SQLstr:='Select x.*,(1月+2月+3月+4月+5月+6月+7月+8月+9月+10月+11月+12月)/12 as 年'
+' from(select left(A,4) as F0,'
+' sum(iif(right(A,2)=''01'',B,0)) as 1月,'
+' sum(iif(right(A,2)=''02'',B,0)) as 2月,'
+' sum(iif(right(A,2)=''03'',B,0)) as 3月,'
+' sum(iif(right(A,2)=''04'',B,0)) as 4月,'
+' sum(iif(right(A,2)=''05'',B,0)) as 5月,'
+' sum(iif(right(A,2)=''06'',B,0)) as 6月,'
+' sum(iif(right(A,2)=''07'',B,0)) as 7月,'
+' sum(iif(right(A,2)=''08'',B,0)) as 8月,'
+' sum(iif(right(A,2)=''09'',B,0)) as 9月,'
+' sum(iif(right(A,2)=''10'',B,0)) as 10月,'
+' sum(iif(right(A,2)=''11'',B,0)) as 11月,'
+' sum(iif(right(A,2)=''12'',B,0)) as 12月'
+' from(select left(日期,6) as A,sum(VAL(R2020)) as B from R group by left(A,6)) t group by left(A,4)) as x';


执行时说left(日期,6) 不包含合计部分。晕，请指点。




[解决办法]
from(select left(日期,6) as A,sum(VAL(R2020)) as B from R group by left(日期,6)) t group by left(A,4)) as x';