表如下:
id ability1 1
2 1
3 2
4 3
5 2
6 4
7 5
8 4
9 5
10 4根据ability得到一个值:
算法就是(1*1的数量+2*2数量+3*3数量+4*数量+5*5数量)/(1数量+2数量+3数量+4数量+5数量)
:(1*2+2*2+3*1+4*3+5*2)/(2+2+1+3+2)
id ability1 1
2 1
3 2
4 3
5 2
6 4
7 5
8 4
9 5
10 4根据ability得到一个值:
算法就是(1*1的数量+2*2数量+3*3数量+4*数量+5*5数量)/(1数量+2数量+3数量+4数量+5数量)
:(1*2+2*2+3*1+4*3+5*2)/(2+2+1+3+2)
from
(
select ability*count(id) as a,count(id) as num from tb group by ability
)t
自己判断一下除数是否为0
select cast(sum(ability*cnt)*1./nullif(sum(cnt),0) as decimal(12,2)) ret
from (
select ability,count(*) cnt
from tb
group by ability
) t