SQL Server：基于文本字段CSV标签按用户分组统计错误计数优化方案

你可以通过一次关联计算完成所有标签的统计，不需要重复编写UPDATE逻辑，后续新增标签仅需更新#Categories表即可，无需修改统计代码。

通用兼容方案（适配所有支持字符串拼接的SQL数据库） #

-- 标签列表维护逻辑保持不变，新增标签仅需往该表插入数据
CREATE TABLE #Categories (Category varchar(30))
INSERT INTO #Categories (Category)
VALUES ('Agreement')
        ,('Board')
        ,('Budget')
        ,('Conflict')
        ,('Contact')
        ,('Dupe')
        ,('Item')
        ,('SkipDispatch')
        ,('SLAMiss')
        ,('Subtype')
        ,('Type')
        ,('Whitespace')

-- 核心统计逻辑，无需随标签新增修改
WITH WorkflowTags AS (
    SELECT 
        A.[User],
        B.User_Defined_Field_Value AS TagStr
    FROM Tickets_SLA_Workflow A
    LEFT JOIN Tickets C ON A.Tickets_RecID = C.Tickets_RecID
    LEFT JOIN Tickets_User_Defined_Field_Value B 
        ON C.Tickets_RecID = B.Tickets_RecID 
        AND B.User_Defined_Field_RecID = 28
    WHERE A.Date_Responded_UTC BETWEEN @Start AND @End
),
TagCount AS (
    SELECT
        wt.[User],
        c.Category,
        COUNT(*) AS ActualErrors
    FROM WorkflowTags wt
    CROSS JOIN #Categories c
    WHERE wt.TagStr LIKE CONCAT('%', c.Category, '%')
    GROUP BY wt.[User], c.Category
),
AllUserCategory AS (
    SELECT DISTINCT wt.[User], c.Category
    FROM WorkflowTags wt
    CROSS JOIN #Categories c
)
SELECT 
    auc.[User],
    auc.Category,
    ISNULL(tc.ActualErrors, 0) AS Errors
FROM AllUserCategory auc
LEFT JOIN TagCount tc 
    ON auc.[User] = tc.[User] 
    AND auc.Category = tc.Category
ORDER BY auc.[User], auc.Category

精确匹配优化方案（避免子串误判，适用于SQL Server 2016+/MySQL 8.0+等支持字符串拆分的数据库） #

如果存在标签子串重叠的场景（比如同时有Board和BoardTest两个标签，LIKE会导致误匹配），可以先拆分标签字段再做精确匹配，仅需修改TagCount部分逻辑：

TagCount AS (
    SELECT
        wt.[User],
        c.Category,
        COUNT(*) AS ActualErrors
    FROM WorkflowTags wt
    -- 按逗号拆分标签，同时清除标签前后可能的空格、制表符
    CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(wt.TagStr, ' ', ''), CHAR(9), ''), ',') s
    INNER JOIN #Categories c ON s.value = c.Category
    GROUP BY wt.[User], c.Category
)

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