基于M_In表筛选含指定值且排除特定值的UserId的SQL实现

我有一张名为M_In的表，包含UserId和MeFr两列，现在需要筛选出满足以下条件的用户记录：

• 该用户的MeFr列中必须包含值123
• 该用户的MeFr列中不能包含值456（或其他指定的排除值）

示例表数据 #

预期输出 #

基于窗口函数的实现方案 #

我们可以通过窗口函数先对每个UserId进行分组标记，再筛选符合条件的记录，具体代码如下：

WITH user_status AS (
    SELECT
        *,
        -- 标记当前用户是否存在MeFr=123的记录
        MAX(CASE WHEN MeFr = '123' THEN 1 ELSE 0 END) OVER (PARTITION BY UserId) AS has_target,
        -- 标记当前用户是否存在MeFr=456的记录
        MAX(CASE WHEN MeFr = '456' THEN 1 ELSE 0 END) OVER (PARTITION BY UserId) AS has_exclude
    FROM M_In
)
SELECT UserId, MeFr
FROM user_status
WHERE has_target = 1 AND has_exclude = 0;

逻辑说明 #

1. 窗口函数MAX() OVER (PARTITION BY UserId)会对每个用户的所有记录做聚合判断：
   • 如果用户有任意一条MeFr=123，has_target会被设为1
   • 如果用户有任意一条MeFr=456，has_exclude会被设为1
2. 最后通过WHERE子句筛选出同时满足「包含目标值123」且「不包含排除值456」的记录。

如果需要排除多个值（比如456、789），只需修改has_exclude的判断逻辑：

MAX(CASE WHEN MeFr IN ('456', '789') THEN 1 ELSE 0 END) OVER (PARTITION BY UserId) AS has_exclude

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