求解一个求平均值的问题,先谢谢大家了
AA CC DD EE
0002USER012.0 0.0
5260USER210.0 0.0
5260USER210.0 0.0
0002USER112.0 0.0
0002USER112.0 0.0
5260USER310.0 0.0
5260USER410.0 0.0
5260USER410.0 0.0
最后得到的结果如下:
AA CC DD EE
0002USER012.0 4
5260USER210.0 2
5260USER210.0 2
0002USER112.0 4
0002USER112.0 4
5260USER310.0 2
5260USER410.0 2
5260USER410.0 2
即:根据AA的值做为分组的条件, 对DD的值进行平均得出EE的值
在ACCESS中以及在SQLSERVER中如何实现,谢谢
[解决办法]
create table t(AA varchar(10),CC varchar(10),DD int)
insert t
select '0002 ', 'USER0 ',12
union all
select '5260 ', 'USER2 ',10
union all
select '5260 ', 'USER2 ',10
union all
select '0002 ', 'USER1 ',12
union all
select '0002 ', 'USER1 ',12
union all
select '5260 ', 'USER3 ',10
union all
select '5260 ', 'USER4 ',10
unionall
select '5260 ', 'USER4 ',10
select a.aa,a.cc,a.dd,(select dd/count(dd) from t where dd = a.dd group by dd) ee from t a
[解决办法]
create table T(AA varchar(10), CC varchar(10), DD decimal(10,1), EE decimal(10,1))
insert T select '0002 ', 'USER0 ',12.0, 0.0
union all select '5260 ', 'USER2 ',10.0, 0.0
union all select '5260 ', 'USER2 ',10.0, 0.0
union all select '0002 ', 'USER1 ',12.0, 0.0
union all select '0002 ', 'USER1 ',12.0, 0.0
union all select '5260 ', 'USER3 ',10.0, 0.0
union all select '5260 ', 'USER4 ',10.0, 0.0
union all select '5260 ', 'USER4 ',10.0, 0.0
select AA, CC, DD,
EE=cast(DD as int)/(select count(*) from T where AA=tmp.AA)
from T as tmp
--result
AA CC DD EE
---------- ---------- ------------ -----------
0002 USER0 12.0 4
5260 USER2 10.0 2
5260 USER2 10.0 2
0002 USER1 12.0 4
0002 USER1 12.0 4
5260 USER3 10.0 2
5260 USER4 10.0 2
5260 USER4 10.0 2
(8 row(s) affected)
[解决办法]
create table T(AA varchar(10), CC varchar(10), DD decimal(10,1), EE decimal(10,1))
insert T select '0002 ', 'USER0 ', 12.0, 0.0
union all select '5260 ', 'USER2 ', 10.0, 0.0
union all select '5260 ', 'USER2 ', 10.0, 0.0
union all select '0002 ', 'USER1 ', 12.0, 0.0
union all select '0002 ', 'USER1 ', 12.0, 0.0
union all select '5260 ', 'USER3 ', 10.0, 0.0
union all select '5260 ', 'USER4 ', 10.0, 0.0
union all select '5260 ', 'USER4 ', 10.0, 0.0
select distinct AA, CC, DD,
EE=cast(DD as int)/(select count(*) from T where AA=tmp.AA)
from T
[解决办法]
他们这样写是让你更明白点,
具体的sql语句就是这样的,和他们的一样!
select AA, CC, DD,
EE=cast(DD as int)/(select count(*) from T where AA=tmp.AA )
from T as tmp