SQL关联数据编码为新列时CASE WHEN返回多值的问题咨询

你当前同一ID返回多条不同状态记录的核心原因是：左关联的表b、表c均为单ID对应多条记录的结构，直接关联后会产生笛卡尔积，每条关联结果都会单独执行CASE WHEN逻辑生成状态值，DISTINCT仅能对完全重复的行做去重，无法处理同一ID对应不同状态的场景。

核心思路是先对关联的多记录表按ID做聚合，保证每个ID仅返回1行聚合结果，再和主表关联计算最终状态，避免笛卡尔积产生的多记录问题。

操作步骤 #

1. 先对优先级更高的内部表b按ID分组，用聚合函数标记每个ID是否命中各类状态，确保每个ID在b表的处理结果仅1行
2. 同理对第三方表c按ID分组，聚合标记每个ID对应的各类状态，确保每个ID在c表的处理结果仅1行
3. 将聚合后的b、c表和主表a关联，再执行原有CASE WHEN逻辑即可得到每个ID唯一的最高优先级状态

修改后代码示例 #

-- 聚合内部表b：每个ID仅返回1行，标记各状态是否命中
WITH b_agg AS (
    SELECT 
        id,
        MAX(CASE WHEN Went_Member >= 1 THEN 1 ELSE 0 END) AS has_member,
        MAX(CASE WHEN Went_NonMember >= 1 THEN 1 ELSE 0 END) AS has_attend_nonmember,
        MAX(CASE WHEN Going_NonMember >= 1 THEN 1 ELSE 0 END) AS has_going_nonmember,
        MAX(CASE WHEN OptOut = '1' THEN 1 ELSE 0 END) AS has_optout,
        MAX(CASE WHEN Cancelled >= 1 THEN 1 ELSE 0 END) AS has_cancelled
    FROM TableWithMemberStatus1
    GROUP BY id
),
-- 聚合第三方表c：每个ID仅返回1行，标记各状态是否命中
c_agg AS (
    SELECT 
        id,
        MAX(CASE WHEN MemberStatus = '9' THEN 1 ELSE 0 END) AS c_has_member,
        MAX(CASE WHEN MemberStatus = '6' THEN 1 ELSE 0 END) AS c_has_attend_nonmember,
        MAX(CASE WHEN DateBooked > CURRENT_TIMESTAMP THEN 1 ELSE 0 END) AS c_has_going_nonmember,
        MAX(CASE WHEN OptOut = '1' THEN 1 ELSE 0 END) AS c_has_optout,
        MAX(CASE WHEN MemberStatus = '8' THEN 1 ELSE 0 END) AS c_has_cancelled
    FROM TableWithMemberStatus2
    GROUP BY id
)
SELECT DISTINCT
    a.ticketnumber,
    a.id,
    -- 此处补充你需要的其他表字段
    CASE
        WHEN b_agg.has_member = 1 THEN 'Member'
        WHEN b_agg.has_attend_nonmember = 1 THEN 'Attended but not member'
        WHEN b_agg.has_going_nonmember = 1 THEN 'Going but not member'
        WHEN b_agg.has_optout = 1 THEN 'Opt Out'
        WHEN b_agg.has_cancelled = 1 THEN 'Cancelled'
        WHEN c_agg.c_has_member = 1 THEN 'Member'
        WHEN c_agg.c_has_attend_nonmember = 1 THEN 'Attended but not member'
        WHEN c_agg.c_has_going_nonmember = 1 THEN 'Going but not member'
        WHEN c_agg.c_has_optout = 1 THEN 'Opt out'
        WHEN c_agg.c_has_cancelled = 1 THEN 'Cancelled'
        ELSE NULL -- 可根据业务需求补充默认状态
    END AS NewMemberStatus
FROM table1 a
LEFT JOIN b_agg ON a.id = b_agg.id
LEFT JOIN c_agg ON a.id = c_agg.id
-- 其他关联表如果也存在单ID多记录的情况，也建议先做聚合再关联
ORDER BY a.ticketnumber

如果后续状态优先级调整频繁，可给每个状态赋予固定权重值（比如Member权重为10，Attended but not member权重为9，以此类推），聚合时直接取每个ID对应的最高权重值，再映射为对应状态即可，无需依赖CASE WHEN的顺序维护优先级。

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