含排除条件的日期范围SQL查询：需求实现与自解方案

第一部分：基础查询 #

现有Clients数据表，包含Last_Seen字段，需求是查询距今180天至173天这7天窗口内的客户记录，已实现的SQL如下：

SELECT DISTINCT *
FROM Clients
WHERE Last_Seen BETWEEN CURDATE() - INTERVAL 180 DAY
                    AND CURDATE() - INTERVAL 173 DAY
AND Last_Seen IS NOT NULL

第二部分：进阶需求与实现 #

需求说明 #

在基础查询结果中，排除两类客户：

• 该7天窗口之后有Last_Seen记录的客户
• 该7天窗口之后Scheduled字段有预约的客户

自行实现的SQL语句 #

SELECT DISTINCT *
FROM Clients
WHERE Last_Seen BETWEEN CURDATE() - INTERVAL 180 DAY
                    AND CURDATE() - INTERVAL 173 DAY
AND Last_Seen IS NOT NULL
AND Last_Seen NOT BETWEEN CURDATE() - INTERVAL 173 DAY
                      AND CURDATE() - INTERVAL 1 DAY
AND Scheduled NOT BETWEEN CURDATE() - INTERVAL 173 DAY
                      AND CURDATE() - INTERVAL 1 DAY
AND Last_Seen NOT BETWEEN CURDATE() - INTERVAL 172 DAY
                      AND CURDATE() - INTERVAL 1 DAY
AND Scheduled NOT BETWEEN NOW()
                      AND (NOW() + INTERVAL 365 DAY)

优化建议 #

原SQL存在冗余条件（如重复限制Last_Seen不在窗口之后），且Scheduled的范围逻辑可简化。优化后的SQL更简洁直观：

SELECT DISTINCT c.*
FROM Clients c
WHERE c.Last_Seen BETWEEN CURDATE() - INTERVAL 180 DAY
                      AND CURDATE() - INTERVAL 173 DAY
  AND c.Last_Seen IS NOT NULL
  -- 排除窗口之后有后续Last_Seen记录的客户
  AND NOT EXISTS (
    SELECT 1
    FROM Clients c2
    WHERE c2.id = c.id
      AND c2.Last_Seen > CURDATE() - INTERVAL 173 DAY
  )
  -- 排除窗口之后有Scheduled预约的客户（含过去及未来）
  AND NOT EXISTS (
    SELECT 1
    FROM Clients c3
    WHERE c3.id = c.id
      AND c3.Scheduled > CURDATE() - INTERVAL 173 DAY
      AND c3.Scheduled IS NOT NULL
  )

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