sql怎样使平均分选择性计算

[dswcy]

r_id , r_answer_name , r_user_name , r_scores , r_result , r_q_order
23,张三,被评人,5.0,a,1
24,张三,被评人,0.0,a,2
25,张三,被评人,5.0,a,3
26,张三,被评人,5.0,a,4
27,张三,被评人,5.0,a,5
28,张三,被评人,5.0,a,6
29,张三,被评人,5.0,a,7
30,张三,被评人,5.0,a,8
31,张三,被评人,5.0,a,9
32,张三,被评人,5.0,a,10
33,张三,被评人,0.0,a,11
34,李四,被评人,2.0,d,1
35,李四,被评人,0.0,d,2
36,李四,被评人,2.0,d,3
37,李四,被评人,0.0,d,4
38,李四,被评人,2.0,d,5
39,李四,被评人,2.0,d,6
40,李四,被评人,2.0,d,7
41,李四,被评人,2.0,d,8
42,李四,被评人,2.0,d,9
43,李四,被评人,2.0,d,10
44,李四,被评人,2.0,d,11


请问如果一个人的r_scores结果为0时，他的数值不加入计算，只算不为零的人的值。怎么写。

[dswcy]

select avg(r_scores) from results where r_user_name='被评人' and r_scores<>0 group by r_q_order

select avg(r_scores) from results where r_user_name='被评人' group by r_q_order

这么写都不行

[renliquan]

这样肯定不行撒。把字段类型转换一下。
你字段里是"0.0"，而你的sql语句是："0"
我想应该是这样吧。

[AcHerat]

有那些出入，怎么不行呢？

[dswcy]

写成 "0.0" 查询 查不到结果了

[dawugui]

--这个是可以的.
select r_q_order , avg(r_scores) from results where r_user_name='被评人' and r_scores<>0 group by r_q_order

或者:
select r_q_order , avg(r_scores) from
(
select * from results where r_user_name='被评人' and r_scores<>0
) t
group by r_q_order

[AcHerat]

declare @i decimal(12,2)
set @i = 0

select @i

select case when @i = 0 then 1 else 0 end as [1],
	   case when @i = 0.0 then 1 else 0 end as [2]

/******************

---------------------------------------
0.00

(1 行受影响)

1           2
----------- -----------
1           1

(1 行受影响)

[Rotel-刘志东]

select r_q_order ,avg(r_scores) from reseults 
where r_user_name='被评人' and r_scores<>0 
group by r_q_order

[Rotel-刘志东]

select r_q_order , avg(r_scores) 
from results 
where r_user_name='被评人' and r_scores<>0 
group by r_q_order

[dswcy]

无结果- -

[dongsheng10101]

怎么个不行法？
那个r_scores是小数型吗