以下内容基于mysql8.0进行讲解
一、背景
mysql中count与distinct组合使用的时候,如果distinct的列为null, 则返回了0,这与我的预期结果不符,因此研究一下。
1. 数据准备
drop table user;
CREATE TABLE `user` (
`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
`name` varchar(10) DEFAULT NULL COMMENT '姓名',
`class` varchar(10) DEFAULT NULL COMMENT '班级'
) ENGINE=InnoDB AUTO_INCREMENT=1;
insert into user(name,class) values
('a',null),
('b',null),
('c',null);
2. 问题复现
我们通过对class进行去重,预期结果返回1, 但是实际返回了0.
select count(distinct class) from user;
试试使用name,class一起去重,同样返回了0
select count(distinct name,class) from user;
二、问题原因
-
COUNT()、MIN()和SUM()忽略NULL值。
-
COUNT(*)是一个例外,它计算行数,而不是单个列值。
三、解决方法
那么当count与distinct需要一起使用的时候,如何将null值计算进去呢?
方式一:子查询
可以使用子查询的方式,先对数据进行去重,然后再count(*) 即可得到结果。
select count(*) from (select distinct name,class from user) as tmp
方式二:对空值进行转换
可以通过IFNULL函数对null值进行判断,当为null时返回其他字符,这样就可以参与计算了。
注意:IFNULL(class,'0')返回的0不要与真实数据重复,否则就被去重掉了
select count(distinct name,IFNULL(class,'0')) from user;