MySQL中Case/Count/Distinct组合查询结果异常问题排查

出错的查询语句 #

select CarrierID, CarrierName,
count(distinct case when CaseYear = '2022' then CaseNumber Else 0 end) as '2022',
count(distinct case when CaseYear = '2021' then CaseNumber Else 0 end) as '2021',
count(distinct case when CaseYear = '2020' then CaseNumber Else 0 end) as '2020'
from SalesRegister where CarrierName like 'Grange%'
group by CarrierName order by 3 desc, 1 asc;

表结构与验证数据 #

SalesRegister表包含字段：CarrierID、CarrierName、CaseNumber、CaseYear、CaseMonth。

执行验证查询：

select CaseNumber, CaseYear, CaseMonth from SalesRegister where CarrierName like 'Grange%' order by CaseYear desc, CaseMonth asc;

得到的记录如下：

从数据可知：2020年有2条有效记录，2019年有9条，2021、2022年无相关记录。

错误的查询结果 #

执行出错的查询后，得到结果：

但执行单年查询（如2020年）：

select CaseYear, count(CaseNumber) from SalesRegister
where CarrierName like 'Grange%' and CaseYear = '2020'
group by CaseYear;

能得到正确结果（2020年计数为2）。

问题原因分析 #

错误根源在CASE表达式的ELSE 0部分：

• 当CaseYear不匹配目标年份时，表达式返回0，而COUNT(DISTINCT)会将这个0视为一个有效的非NULL值进行统计。
• 以2022年为例：所有非2022年的记录都会返回0，去重后只有1个唯一值，所以计数为1；
• 2020年的情况：符合条件的2个CaseNumber加上所有非2020年记录返回的0，去重后共3个唯一值，因此计数为3，完全不符合预期。

修正后的查询 #

将CASE表达式中的ELSE 0改为ELSE NULL（或者省略ELSE，因为CASE默认返回NULL），因为COUNT函数会自动忽略NULL值，只会统计匹配年份的CaseNumber去重数量：

select CarrierID, CarrierName,
count(distinct case when CaseYear = '2022' then CaseNumber end) as '2022',
count(distinct case when CaseYear = '2021' then CaseNumber end) as '2021',
count(distinct case when CaseYear = '2020' then CaseNumber end) as '2020'
from SalesRegister where CarrierName like 'Grange%'
group by CarrierName order by 3 desc, 1 asc;

这样就能得到正确的结果：2022和2021年计数为0，2020年计数为2。

内容的提问来源于stack exchange，提问作者retheiss