如何查询已接收消息但未通过任何渠道打开的用户ID？

要实现你的需求，我们需要先定位所有接收了目标消息的用户（在msg_email或msg_voice中有对应记录），再排除那些在任一渠道打开过消息的用户，剩下的就是符合条件的用户。

方法一：使用IN和NOT IN子查询（可读性强） #

SELECT person_id, last_name, first_name 
FROM person 
WHERE person_id IN (
    -- 第一步：获取所有接收了该消息的用户（任一渠道有记录）
    SELECT DISTINCT person_id FROM (
        SELECT person_id FROM msg_email WHERE message_id = ?
        UNION ALL
        SELECT person_id FROM msg_voice WHERE message_id = ?
    ) AS all_recipients
)
AND person_id NOT IN (
    -- 第二步：排除在任一渠道打开过消息的用户
    SELECT DISTINCT person_id FROM (
        SELECT person_id FROM msg_email WHERE message_id = ? AND opened = 'Y'
        UNION ALL
        SELECT person_id FROM msg_voice WHERE message_id = ? AND opened = 'Y'
    ) AS opened_users
)
ORDER BY last_name ASC, first_name ASC

方法二：使用LEFT JOIN（性能更优，适合大数据量） #

如果你的数据量较大，用JOIN的方式通常比嵌套子查询更高效：

SELECT p.person_id, p.last_name, p.first_name
FROM person p
-- 关联所有接收消息的用户
JOIN (
    SELECT DISTINCT person_id FROM (
        SELECT person_id FROM msg_email WHERE message_id = ?
        UNION ALL
        SELECT person_id FROM msg_voice WHERE message_id = ?
    ) AS all_recipients
) r ON p.person_id = r.person_id
-- 左连接已打开邮件的用户
LEFT JOIN (
    SELECT person_id FROM msg_email WHERE message_id = ? AND opened = 'Y'
) e ON p.person_id = e.person_id
-- 左连接已收听语音消息的用户
LEFT JOIN (
    SELECT person_id FROM msg_voice WHERE message_id = ? AND opened = 'Y'
) v ON p.person_id = v.person_id
-- 筛选出两个渠道都未打开的用户
WHERE e.person_id IS NULL AND v.person_id IS NULL
ORDER BY p.last_name ASC, first_name ASC

针对你的示例数据的验证 #

• 对于message_id=1，所有接收用户是ID1（Joe）和ID2（Tom）
• 已打开消息的用户是ID2（在msg_email中opened='Y'）
• 最终筛选后仅返回ID1的Joe Smith，完全符合你的期望。

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