编写SQL查询：仅返回订阅了所有有效作者的收件人邮箱

没问题，这个需求完全可以用单条SQL搞定，我帮你梳理思路并写出具体的查询语句。

首先，我们的核心目标是：找出那些订阅了每一位当前有效作者的邮箱。要实现这个，需要完成两步关键计算：

• 算出当前所有有效作者的总数
• 统计每个邮箱订阅的有效作者数量，筛选出数量等于总数的邮箱

基础版本（仅基于subscriptions表） #

如果只需要从subscriptions表中筛选符合条件的邮箱，可以用这条查询：

SELECT s.email
FROM subscriptions s
-- 关联authors表，只保留订阅有效作者的记录
JOIN authors a ON s.author_id = a.id
WHERE CURRENT_DATE BETWEEN a.effective_date AND a.retirement_date
-- 按邮箱分组，统计每个邮箱订阅的有效作者数
GROUP BY s.email
-- 筛选出订阅数量等于有效作者总数的邮箱
HAVING COUNT(DISTINCT s.author_id) = (
    SELECT COUNT(*) 
    FROM authors 
    WHERE CURRENT_DATE BETWEEN effective_date AND retirement_date
);

包含recipients表的版本（匹配你的query_1描述） #

如果需要限定邮箱必须存在于recipients表中，只需要把查询和recipients表关联起来：

SELECT r.email
FROM recipients r
-- 关联订阅表
JOIN subscriptions s ON r.email = s.email
-- 关联作者表过滤有效作者
JOIN authors a ON s.author_id = a.id
WHERE CURRENT_DATE BETWEEN a.effective_date AND a.retirement_date
GROUP BY r.email
HAVING COUNT(DISTINCT s.author_id) = (
    SELECT COUNT(*) 
    FROM authors 
    WHERE CURRENT_DATE BETWEEN effective_date AND retirement_date
);

关键细节说明 #

• 使用COUNT(DISTINCT s.author_id)是为了避免同一个邮箱多次订阅同一个作者导致统计数偏大的情况；如果你的subscriptions表不会有重复记录，也可以简化为COUNT(s.author_id)
• 子查询(SELECT COUNT(*) FROM authors WHERE ...)就是你提到的query_2的变形，用来获取当前有效作者的总数
• JOIN操作确保我们只统计订阅了有效作者的记录，排除订阅已退休作者的情况

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