求月平均问题IFF
上次提问，没人回答，虽然用别的方法实现，但一直郁闷，现重提。
access库，用以下求分组数据各月的平均值，结论不对，但求和却是对的，一直郁闷，现求解请指点。
日期 站点 平均
20000101 长沙 2.5
20000102 长沙 3.8
....
20000201 长沙 5.6
20000202 长沙 7.8
....
20000301 长沙 15.6
20000302 长沙 9.8
.......
20000101 浏阳 3.5
20000103 浏阳 3.5
....
20000201 浏阳 6.6
20000202 浏阳 8.8
.....
20000301 浏阳 13.1
20000302 浏阳 12.8
求站点分组的“平均”字段的逐月月平均：以下语句不对，只有年平均正确。
Select 站点,
AVG(iif(Mid(日期,5,2)='01',平均,0)) as 1月,
AVG(iif(Mid(日期,5,2)='02',平均,0)) as 2月,
AVG(iif(Mid(日期,5,2)='03',平均,0)) as 3月,
AVG(iif(Mid(日期,5,2)='04',平均,0)) as 4月,
AVG(iif(Mid(日期,5,2)='05',平均,0)) as 5月,
AVG(iif(Mid(日期,5,2)='06',平均,0)) as 6月,
AVG(iif(Mid(日期,5,2)='07',平均,0)) as 7月,
AVG(iif(Mid(日期,5,2)='08',平均,0)) as 8月,
AVG(iif(Mid(日期,5,2)='09',平均,0)) as 9月,
AVG(iif(Mid(日期,5,2)='10',平均,0)) as 10月,
AVG(iif(Mid(日期,5,2)='11',平均,0)) as 11月,
AVG(iif(Mid(日期,5,2)='12',平均,0)) as 12月,
AVG(平均) as 年平均
from T
where Mid(日期,1,4)='2000'
group by 站点
在SQL Server下用：AVG(case when Substring(日期,5,2)='01' then 平均 end)) as '1月 '时可行的。
[解决办法]
Select X.S as 站点,Max(k1) as 1月,Max(k2) as 2月,Max(k3) as 3月,Max(k4) as 4月, Max(k5) as 5月, Max(k6) as 6月,Max(k7) as 7月,Max(k8) as 8月,Max(k9) as 9月, Max(k10) as 10月,Max(k11) as 11月,Max(k12) as 12月,Y.Q AS 年平均from (Select S, AVG(iif(Mid(D,5,2)='01',A,0)) as k1,AVG(iif(Mid(D,5,2)='02',Q,0)) as k2,AVG(iif(Mid(D,5,2)='03',Q,0)) as k3,AVG(iif(Mid(D,5,2)='04',Q,0)) as k4,AVG(iif(Mid(D,5,2)='05',Q,0)) as k5,AVG(iif(Mid(D,5,2)='06',Q,0)) as k6,AVG(iif(Mid(D,5,2)='07',Q,0)) as k7,AVG(iif(Mid(D,5,2)='08',Q,0)) as k8,AVG(iif(Mid(D,5,2)='09',Q,0)) as k9,AVG(iif(Mid(D,5,2)='10',Q,0)) as k10,AVG(iif(Mid(D,5,2)='11',Q,0)) as k11,AVG(iif(Mid(D,5,2)='12',Q,0)) as k12from T where Mid(D,1,4)='2000'group by S,Mid(D,5,2)) as Xleft join (Select S,Avg(Q) AS Q From T Where Mid(D,1,4)='2000' group by S) as Yon X.S=Y.S Group By X.S,Y.Q/*字段说明=>> D:日期 S:站点 Q:平均结果站点 1月 2月 3月 ... 年平均---------------------------长沙 3.15 6.7 12.7 ... 7.52浏阳 3.5 7.7 12.95 ... 8.05*/
