MySQL版本为5.6
大部分运算会忽略NULL
行,个别有例外(如聚合、排序)。
具体见下表:
如果就是要统计NULL行,可以利用
if
求和解决:sum(if(column is null, 1, 0))
样例如下:
mysql> create table test_count(v int);mysql> insert into test_count values (1), (2), (NULL);mysql> select * from test_count;+------+| v |+------+| 1 || 2 || NULL |+------+select count(*), count(1), count(v), sum(if(v is null, 1, 0)) countOfNull, sum(v), avg(v), min(v), max(v) from test_count;+----------+----------+----------+-------------+--------+--------+--------+--------+| count(*) | count(1) | count(v) | countOfNull | sum(v) | avg(v) | min(v) | max(v) |+----------+----------+----------+-------------+--------+--------+--------+--------+| 3 | 3 | 2 | 1 |3 | 1.5000 |1 |2 |+----------+----------+----------+-------------+--------+--------+--------+--------+
MySQL官方文档1 :/doc/refman/5.6/en/problems-with-null.html ↩︎
MySQL官方文档2 :/doc/refman/5.6/en/working-with-null.html ↩︎
如果觉得《NULL如何参与运算和统计(计数求和聚合count/max/sum/group) - MySQL基本函数与命令(二)》对你有帮助,请点赞、收藏,并留下你的观点哦!