SQL中将一对多表转换为一对一表的技术实现咨询

原表结构与数据 #

转换规则 #

• 当对应CommType的Consent和Emailable均为Yes时，列值为Yes
• 当对应CommType的Emailable为No时，列值为No
• 当对应CommType的Emailable为NULL，或无该Member的此CommType行时，列值为NULL

期望结果 #

实现SQL #

SELECT
    `Member ID`,
    -- 处理News列
    MAX(CASE
        WHEN CommType = 'News' AND Consent = 'Yes' AND Emailable = 'Yes' THEN 'Yes'
        WHEN CommType = 'News' AND Emailable = 'No' THEN 'No'
        ELSE NULL
    END) AS News,
    -- 处理Surveys列
    MAX(CASE
        WHEN CommType = 'Surveys' AND Consent = 'Yes' AND Emailable = 'Yes' THEN 'Yes'
        WHEN CommType = 'Surveys' AND Emailable = 'No' THEN 'No'
        ELSE NULL
    END) AS Surveys,
    -- 处理Events列
    MAX(CASE
        WHEN CommType = 'Events' AND Consent = 'Yes' AND Emailable = 'Yes' THEN 'Yes'
        WHEN CommType = 'Events' AND Emailable = 'No' THEN 'No'
        ELSE NULL
    END) AS Events
FROM
    表A
GROUP BY
    `Member ID`
ORDER BY
    `Member ID`;

说明 #

• 使用MAX()聚合函数是因为每个Member ID + CommType唯一，聚合后只会保留对应CommType的唯一判断结果
• 每个CASE分支严格对应转换规则，确保逻辑准确
• 若后续新增CommType，只需复制对应CASE块并修改CommType值即可扩展

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