当前位置：首页 → 问答吧 → access查询按年月统计

access查询按年月统计

时间：2011-12-15

来源：互联网

access库，表中：
日期温度
20000101 2.5
20000102 3.1
。。。。
20111231 8.7

sql语句要求：求任意时间段内（2000年、或2000-2007年）每年各月的平均值，或者求和，结果为：
日期 1月 2月。。。。12月年平均
2000
2001
。。。
2011

sql语句该怎么实现？请指点。谢谢。

作者: cdq872 发布时间: 2011-12-15

Delphi(Pascal) code

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Text:='Select x.*,(F1+F2+F3+F4+F5+F6+F7+F8+F9+F10+F11+F12)/12 as F13'
    +' from(select left(A,4) as F0,'
    +' sum(iif(right(A,2)=''01'',B,0)) as F1,'
    +' sum(iif(right(A,2)=''02'',B,0)) as F2,'
    +' sum(iif(right(A,2)=''03'',B,0)) as F3,'
    +' sum(iif(right(A,2)=''04'',B,0)) as F4,'
    +' sum(iif(right(A,2)=''05'',B,0)) as F5,'
    +' sum(iif(right(A,2)=''06'',B,0)) as F6,'
    +' sum(iif(right(A,2)=''07'',B,0)) as F7,'
    +' sum(iif(right(A,2)=''08'',B,0)) as F8,'
    +' sum(iif(right(A,2)=''09'',B,0)) as F9,'
    +' sum(iif(right(A,2)=''10'',B,0)) as F10,'
    +' sum(iif(right(A,2)=''11'',B,0)) as F11,'
    +' sum(iif(right(A,2)=''12'',B,0)) as F12'
    +' from(select left(日期,6) as A,sum(温度) as B from 表名 group by left(A,6)) t group by left(A,4)) as x';
  ADOQuery1.Open;
end;