1、使用avg函数,不统计为null的值
2、测试过程:
drop table if exists tmp_avg_t;
create table tmp_avg_t(
id bigint unsigned auto_increment comment '',
test_code varchar(20) default '' comment '',
test_val int(11) comment '',
primary key (id)
)comment='mysql测试avg函数'
;
insert into tmp_avg_t(test_code,test_val)
values ('test1',2),('test1',4),('test2',2)
;
select * from tmp_avg_t;
select test_code,avg(test_val)
from tmp_avg_t
group by test_code
;
-插入null值后查看
insert into tmp_avg_t(test_code,test_val)
values ('test1',null)
;
select * from tmp_avg_t;
select test_code,avg(test_val)
from tmp_avg_t
group by test_code
;
update tmp_avg_t set test_val = 0 where id > 0 and test_code='test1' and test_val is null;
select * from tmp_avg_t;
select test_code,avg(test_val)
from tmp_avg_t
group by test_code
;
以上