MySQL 连接/子查询，其中结果由外部表的多个值过滤

Question

我有两个表:

• 用户
• 用户操作

user_action 表具有以下属性:

• ID
• 用户 ID
• 类型
• 时间戳

我有两种不同类型的操作:

• 已打开消息
• 收到消息

现在我的问题是:如何进行查询，以便获取上个月内打开消息的所有用户，但排除过去 2 天内收到消息的所有用户？

我认为这可以通过某种形式的联接来完成，然后使用 WHERE NOT IN 与子查询，但这听起来不是很有效，所以我想知道是否有更好的方法。

Answer 1

我通常倾向于使用 JOIN 而不是 EXIST/NOT EXIST 子查询。

这是一种使用自LEFT JOIN的方法:

SELECT DISTINCT
    user_id
FROM
    user_action ua
    LEFT JOIN ua2 
        ON  ua2.user_id = ua.userid
        AND ua2.type = 'received message'
        AND ua2.timestamp > CURRENT_TIMESTAMP() - INTERVAL 2 DAY
WHERE 
    ua.type = 'opened message'
    AND ua.timestamp > CURRENT_TIMESTAMP() - INTERVAL 1 MONTH
    AND ua2.user_id IS NULL

详细信息:

• ua1 使用 WHERE 子句搜索上个月内打开过消息的用户
• LEFT JOIN ua2 选项尝试查找同一用户在过去两天内收到的消息
• WHERE ... ua2.user_id IS NULL 删除发生此类消息的记录(即 LEFT JOIN 成功的记录)
• SELECT DISTINCT 当给定用户在上个月内收到多条消息时，可以避免重复的输出行