能否在单条MySQL查询中复用查询结果/状态，实现特定用户筛选？

问题背景 #

现有events表结构及数据如下：

需要查询满足以下条件的用户：

• 存在event_type = 'X'的记录
• 在该X事件的date_of_event之后的两年内，没有event_type = 'Y'的记录

根据表格数据，符合条件的用户是userA和userB，以下是几种单条MySQL查询的实现方式：

方法1：使用NOT EXISTS子查询 #

直接针对每个用户的X事件，检查是否不存在符合时间范围的Y事件：

SELECT DISTINCT e1.user_id
FROM events e1
WHERE e1.event_type = 'X'
  AND NOT EXISTS (
    SELECT 1
    FROM events e2
    WHERE e2.user_id = e1.user_id
      AND e2.event_type = 'Y'
      AND e2.date_of_event BETWEEN e1.date_of_event AND DATE_ADD(e1.date_of_event, INTERVAL 2 YEAR)
  );

方法2：使用LEFT JOIN过滤匹配项 #

通过左连接关联X事件和符合时间条件的Y事件，筛选出没有匹配到Y事件的记录：

SELECT DISTINCT e1.user_id
FROM events e1
LEFT JOIN events e2
  ON e2.user_id = e1.user_id
  AND e2.event_type = 'Y'
  AND e2.date_of_event BETWEEN e1.date_of_event AND DATE_ADD(e1.date_of_event, INTERVAL 2 YEAR)
WHERE e1.event_type = 'X'
  AND e2.user_id IS NULL;

方法3：使用窗口函数（MySQL 8.0+） #

如果你的MySQL版本支持窗口函数，可以先为每个用户的X事件匹配后续最近的Y事件，再判断时间间隔：

WITH user_events AS (
  SELECT 
    user_id,
    event_type,
    date_of_event,
    LEAD(CASE WHEN event_type = 'Y' THEN date_of_event END) OVER (
      PARTITION BY user_id ORDER BY date_of_event
    ) AS next_y_date
  FROM events
)
SELECT DISTINCT user_id
FROM user_events
WHERE event_type = 'X'
  AND (next_y_date IS NULL OR DATEDIFF(next_y_date, date_of_event) > 730);

注意：如果你的日期格式是DD-MM-YYYY，建议先转换为MySQL标准的YYYY-MM-DD格式，避免日期解析错误。

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