MySQL 5.7查询：排除6月发送日期≥3条的condensed表记录

问题 #

使用MySQL 5.7版本的phpMyAdmin，数据库内有一张名为condensed的表，存储数百万条唯一邮箱记录及最多14列发送日期（部分发送日期为NULL）。需求是排除所有在6月拥有3个及以上有效发送日期的记录，仅保留6月发送日期数量<3的记录。

示例表结构与数据 #

| email  | a_last_sent | b_last_sent | c_last_sent | d_last_sent | ..up to 14 dates
----------------------------------------------------------------------------------
| email1 | 2024-06-12  | 2024-05-25  |    NULL     | 2024-06-06  |
----------------------------------------------------------------------------------
| email2 | 2024-06-01  | 2024-06-16  |  2024-06-05 | 2024-06-19  |
----------------------------------------------------------------------------------
| email3 |   NULL      |   NULL      |  2024-05-12 | 2024-06-10  |
----------------------------------------------------------------------------------
| email4 |   NULL      | 2024-06-13  |    NULL     | 2024-05-11  |
----------------------------------------------------------------------------------
| email5 | 2024-06-09  | 2024-05-01  |    NULL     |    NULL     |
----------------------------------------------------------------------------------  

期望查询结果 #

| email  | a_last_sent | b_last_sent | c_last_sent | d_last_sent | ..up to 14 dates
----------------------------------------------------------------------------------
| email1 | 2024-06-12  | 2024-05-25  |    NULL     | 2024-06-06  |
----------------------------------------------------------------------------------
| email3 |   NULL      |   NULL      |  2024-05-12 | 2024-06-10  |
----------------------------------------------------------------------------------
| email4 |   NULL      | 2024-06-13  |    NULL     | 2024-05-11  |
----------------------------------------------------------------------------------
| email5 | 2024-06-09  | 2024-05-01  |    NULL     |    NULL     |
----------------------------------------------------------------------------------  

当前错误查询 #

当前编写的查询无法实现计数判断：

SELECT 
  * FROM 
`condensed` 
WHERE 
(
  `a_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30'
  OR
  `b_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30'
  OR
  `c_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30' 
  // remaining date columns     
)

解决方案 #

要实现按列计数6月有效日期的逻辑，可通过CASE语句对每一列做月份判断，累加符合条件的列数后筛选出总数小于3的记录。

正确SQL语句 #

SELECT 
  * 
FROM 
  `condensed` 
WHERE 
  (
    CASE WHEN `a_last_sent` BETWEEN '2024-06-01' AND '2024-06-30' THEN 1 ELSE 0 END +
    CASE WHEN `b_last_sent` BETWEEN '2024-06-01' AND '2024-06-30' THEN 1 ELSE 0 END +
    CASE WHEN `c_last_sent` BETWEEN '2024-06-01' AND '2024-06-30' THEN 1 ELSE 0 END +
    CASE WHEN `d_last_sent` BETWEEN '2024-06-01' AND '2024-06-30' THEN 1 ELSE 0 END +
    -- 依次添加剩余10个日期列的判断语句
    CASE WHEN `n_last_sent` BETWEEN '2024-06-01' AND '2024-06-30' THEN 1 ELSE 0 END
  ) < 3;

逻辑说明 #

• 每个CASE语句单独判断对应列的日期是否在2024年6月范围内，符合条件返回1，否则返回0（NULL值会被判定为不符合，返回0）
• 所有CASE结果相加，得到该邮箱在6月的有效发送日期总数
• 通过WHERE子句筛选出总数小于3的记录，自动排除总数≥3的记录（如示例中的email2）

性能优化提示 #

针对数百万条数据的场景，可通过以下方式提升查询效率：

• 给经常用于月份筛选的日期列添加单列索引，或根据查询频次创建合适的联合索引
• 避免使用SELECT *，明确指定需要的列以减少数据传输量
• 用EXPLAIN语句分析查询计划，确认索引是否被有效调用

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