SQL Server 2016字符串聚合：筛选CONFIG中不存在的消息类型

SELECT 
    PARTNER.ID AS [ID],
    PARTNER.NAME AS [name],
    PARTNER.GLN AS [GLN],
    IIF(CONFIG.DIRECTION=0,'IN','OUT') AS [Direction],
    COUNT(CONFIG.ID) AS [count config],
    -- 拼接当前PARTNER+Direction下CONFIG中缺失的消息类型
    STUFF((
        SELECT ', ' + mt.message_type
        FROM message_type mt
        -- 过滤掉不需要考虑的消息类型ID
        WHERE mt.id NOT IN (0,7,8,11,12,14,15)
          -- 判断该消息类型是否未在对应PARTNER+Direction的CONFIG中配置
          AND NOT EXISTS (
              SELECT 1 
              FROM CONFIG c
              WHERE c.PARTNER_ID_EXT = PARTNER.ID
                AND c.DIRECTION = CONFIG.DIRECTION
                AND c.MESSAGE_TYPE_ID = mt.id
          )
        ORDER BY mt.id
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)'), 1, 2, '') AS [Missing Message Types]
FROM 
    PARTNER 
LEFT JOIN CONFIG ON PARTNER.ID = CONFIG.PARTNER_ID_EXT 
WHERE 
    PARTNER.ID IS NOT NULL 
    AND PARTNER.CHARSET IS NOT NULL
    AND PARTNER.NAME NOT LIKE 'TEST%'
GROUP BY 
    PARTNER.ID, PARTNER.NAME, PARTNER.GLN, CONFIG.DIRECTION
HAVING 
    COUNT(CONFIG.ID) NOT IN (5,11,16,20)
ORDER BY 
    [name] ASC, [Direction] ASC

关键修改说明 #

• 原查询的子查询是固定返回所有符合过滤条件的消息类型，现在改成针对每个合作方和消息方向，通过NOT EXISTS精准判断该消息类型是否未在对应CONFIG记录中存在
• 调整了STUFF的截断长度：原代码拼接后截断1个字符，实际应该截断2个字符（对应, 的长度），避免结果开头残留空格
• 添加了别名[Missing Message Types]，明确缺失类型列的含义

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