MySQL查询结果异常：分年份与总统计distinct count值不符

分年份统计结果 #

执行分年份的distinct计数查询，结果如下：

mysql> select count(distinct message_id) from table1 where message_notification=0 and YEAR(created_at) = '2024';

| count(distinct message_id) |
|                         33 |


mysql> select count(distinct message_id) from table1 where message_notification=0 and YEAR(created_at) = '2023';

| count(distinct message_id) |
|                       5796 |

1 row in set (0.07 sec)

mysql> select count(distinct message_id) from table1 where message_notification=0 and YEAR(created_at) = '2022';

| count(distinct message_id) |
|                        431 |

1 row in set (0.03 sec)

三个年份统计值之和：431 + 5796 + 33 = 6260

整体统计结果 #

直接查询所有符合条件的distinct计数，结果如下：

mysql> select count(distinct message_id) from table1 where message_notification=0;
| count(distinct message_id) |
|                       6259 |

1 row in set (0.05 sec)

已完成的排查项 #

• 无created_at或message_notification为NULL的记录
• 无created_at早于2022或晚于2024的记录（通过升序、降序查询created_at验证）

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