求助：SQL Server多表关联并聚合匹配KB工单编号

我有三个SQL Server表：

• INC - 事件工单（Incident Tickets）
• INT - 交互工单（Interaction Tickets）
• KB - 知识库文章浏览记录（Knowledge article views）

三个表均包含user ID、ticket number、timestamp字段。需要开发报表，识别KB中与INC或INT拥有相同user ID且日期一致的记录，最终输出为INC与INT的联合结果，新增一列用逗号分隔列出匹配的KB工单编号。

示例数据 #

INC表 #

KB表 #

期望输出 #

现状与问题 #

最终输出需导入PowerBI，最初尝试用Power Query处理，但各表数据量超100万行，耗时48小时以上仍未完成，故转用SQL查询。当前写出的SQL可关联三表，但仅返回单个匹配行，需使用STRING_AGG函数但无法正确实现，现有SQL代码如下：

select 
    inc.TicketNumber, inc.OpenTime, inc.Contact,
    kb.KBTicketNumber, kb.UpdateTime, kb.ViewedMMID
from 
    MMITMetrics.dbo.INC_IncidentTickets inc
full join  
    MMITMetrics.dbo.KB_Use kb on inc.Contact = kb.ViewedMMID 
                              and cast(inc.OpenTime as date) = cast(kb.UpdateTime as date)
where 
    inc.OpenTime > '2021-01-01 12:00:00.000' 
    or kb.UpdateTime > '2021-01-01 12:00:00.000'

union 

select 
    int.TicketNumber, int.OpenTime,int.Contact,
    kb.KBTicketNumber, kb.UpdateTime, kb.ViewedMMID
from 
    MMITMetrics.dbo.INT_InteractionTickets int 
full join  
    MMITMetrics.dbo.KB_Use kb on int.Contact = kb.ViewedMMID 
                              and cast(int.OpenTime as date) = cast(kb.UpdateTime as date)
where 
    int.OpenTime > '2021-01-01 12:00:00.000' 
    or kb.UpdateTime > '2021-01-01 12:00:00.000'

解决方案 #

优化后的SQL代码 #

-- 合并INC和INT的工单数据，统一字段名
WITH CombinedTickets AS (
    SELECT 
        TicketNumber, 
        Contact AS UserID, 
        CAST(OpenTime AS DATE) AS TicketDate
    FROM MMITMetrics.dbo.INC_IncidentTickets
    WHERE OpenTime > '2021-01-01 12:00:00.000'
    
    UNION ALL
    
    SELECT 
        TicketNumber, 
        Contact AS UserID, 
        CAST(OpenTime AS DATE) AS TicketDate
    FROM MMITMetrics.dbo.INT_InteractionTickets
    WHERE OpenTime > '2021-01-01 12:00:00.000'
),
-- 预聚合KB表中每个用户+日期对应的工单编号列表
AggregatedKB AS (
    SELECT 
        ViewedMMID AS UserID, 
        CAST(UpdateTime AS DATE) AS KBDate,
        STRING_AGG(KBTicketNumber, ',') WITHIN GROUP (ORDER BY KBTicketNumber) AS KBTickets
    FROM MMITMetrics.dbo.KB_Use
    WHERE UpdateTime > '2021-01-01 12:00:00.000'
    GROUP BY ViewedMMID, CAST(UpdateTime AS DATE)
)
-- 关联合并后的工单和预聚合的KB数据
SELECT 
    ct.TicketNumber,
    ct.UserID,
    ct.TicketDate,
    ISNULL(ak.KBTickets, '') AS KBTickets
FROM CombinedTickets ct
LEFT JOIN AggregatedKB ak 
    ON ct.UserID = ak.UserID 
    AND ct.TicketDate = ak.KBDate

关键说明 #

1. 拆分逻辑提升可读性：用CTE分别处理工单合并和KB数据聚合，避免重复代码，逻辑更清晰。
2. STRING_AGG正确用法：必须配合GROUP BY按用户ID+日期分组，将同组的KB工单编号聚合为逗号分隔的字符串。
3. 百万级数据性能优化：
   • 提前过滤时间条件，减少后续处理的数据量；
   • 预聚合KB表，避免关联后再聚合带来的性能损耗；
   • 用UNION ALL替代UNION（无需去重时），减少不必要的去重计算；
   • 确保Contact、OpenTime、UpdateTime字段有合适的索引，加速关联和过滤。
4. 保留所有工单记录：用LEFT JOIN确保即使没有匹配的KB记录，工单数据也会被保留，KB工单列表显示为空字符串。

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