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时,他的数值不加入计算,只算不为零的人的值。怎么写。
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时,他的数值不加入计算,只算不为零的人的值。怎么写。
你字段里是"0.0",而你的sql语句是:"0"
我想应该是这样吧。
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
declare @i decimal(12,2)
set @i = 0select @iselect 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 行受影响)
where r_user_name='被评人' and r_scores<>0
group by r_q_order
from results
where r_user_name='被评人' and r_scores<>0
group by r_q_order
那个r_scores是小数型吗