SQL如何筛选同时有activate和recurring两类action记录的用户数据

核心逻辑 #

要找的是用户维度同时持有两类action值的所有关联记录，不是单条记录同时匹配两个action值，不能直接在WHERE层对单条记录做双值匹配，需要先圈定符合条件的用户范围，再关联查询对应全量记录。

通用兼容方案（支持所有SQL数据库） #

通过分组聚合筛选出符合条件的用户名，再关联原表拉取全量记录：

SELECT t.*
FROM tbl_transactions t
INNER JOIN (
    SELECT name
    FROM tbl_transactions
    WHERE action IN ('activate', 'recurring')
    GROUP BY name
    HAVING COUNT(DISTINCT action) = 2
) matched_user ON t.name = matched_user.name;

这个写法完全匹配示例数据的预期结果：

• John同时存在两类action，名下2条记录全部返回
• Bill同时存在两类action，名下3条记录全部返回
• Salah仅存在activate单类action，名下记录全部被过滤

支持窗口函数的简化写法（MySQL8.0+、PostgreSQL、SQL Server等适用） #

不用做表关联，通过窗口函数按用户维度统计匹配的action种类数，直接筛选：

WITH user_tag AS (
    SELECT
        *,
        COUNT(DISTINCT CASE WHEN action IN ('activate', 'recurring') THEN action END)
            OVER (PARTITION BY name) AS match_action_count
    FROM tbl_transactions
)
SELECT id, name, action
FROM user_tag
WHERE match_action_count = 2;

常见避坑 #

不要写WHERE action = 'activate' AND action = 'recurring'这类条件，单条记录的action字段不可能同时等于两个不同值，该写法永远返回空结果。

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