MySQL 5.7查询：获取活跃列表数≥3的邮箱全量记录

需求背景 #

现有一个VIEW结构如下，其中sent_last_30_days字段值为1时，表示该邮箱在对应列表处于活跃状态。我们需要找出活跃列表数≥3的邮箱，并返回这些邮箱的所有记录。

原VIEW数据 #

| email  | list  | sent_last_30_days |
--------------------------------------
| email1 | list1 |       1           |
| email2 | list1 |       0           |
| email1 | list2 |       1           |
| email3 | list3 |       1           |
| email1 | list3 |       1           |
| email2 | list2 |       0           |
| email1 | list4 |       0           |
| email2 | list3 |       1           |
--------------------------------------

目标结果示例 #

| email  | list  | sent_last_30_days |
--------------------------------------
| email1 | list1 |       1           |
| email1 | list2 |       1           |
| email1 | list3 |       1           |
| email1 | list4 |       0           |
--------------------------------------

解决方案 #

由于MySQL 5.7不支持窗口函数，我们可以通过子查询统计活跃数+关联原表的方式实现需求：

方法1：使用INNER JOIN

SELECT v.*
FROM your_view_name v
INNER JOIN (
    -- 统计每个邮箱的活跃列表数，筛选出≥3的邮箱
    SELECT email
    FROM your_view_name
    WHERE sent_last_30_days = 1
    GROUP BY email
    HAVING COUNT(*) >= 3
) active_email ON v.email = active_email.email;

方法2：使用WHERE EXISTS

SELECT v.*
FROM your_view_name v
WHERE EXISTS (
    -- 判断当前邮箱的活跃列表数是否≥3
    SELECT 1
    FROM your_view_name v2
    WHERE v2.email = v.email
      AND v2.sent_last_30_days = 1
    GROUP BY v2.email
    HAVING COUNT(*) >= 3
);

说明 #

• 两种写法逻辑一致，都能得到目标结果。INNER JOIN的子查询仅执行一次，在数据量较大时通常性能更优；WHERE EXISTS是逐行判断匹配，逻辑更直观。
• 注意将语句中的your_view_name替换为实际的VIEW名称。

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