SQL Server:根据文本字段中的csv值按用户分组的总数

Question

我不知道如何最好地用语言来描述我的问题，没有前后的例子，这可能是我在这里找不到现有解决方案的原因。

我有'标签'在一个用户定义的字段在我们的门票数据库，以表示处理说票时所做的错误。该字段可以为空，可以有一个条目，也可以有多个条目。它们应该用逗号分隔，但是我无法在输入文本时验证文本。下面的临时表#Categories显示了我希望在这个字段中出现的当前"标签"。

我可以用下面的方法得到我想要的结果，但我觉得这是笨拙的，几乎可以肯定有一个更优雅的解决方案，不需要一遍又一遍运行相同的更新命令，手动更改它所匹配的类别。这也将需要手动更新，如果一个新的"标签"被引入(或者如果有人打错了一个标签，因此没有结果匹配)。

我想要的结果是一个用户列表，带有'错误'计数，即在指定的日期范围内(包括0)的所有票证的所有用户定义字段中出现这些标签，如下所示。理想的解决方案应该包含新用户。在没有人工干预的情况下创建新的类别(标签)，这样我就可以在生成的报告中看到它们。

<表类> 用户 类别错误tbody><<tr>jdoe板0jdoe预算1jdoe冲突0jdoe与2jdoe欺骗1jdoe项目0jdoeSkipDispatch0jdoeSLAMiss0jdoe亚型5jdoe类型0jdoe空白0jsmith板0jsmith预算0jsmith冲突1jsmith与0jsmith欺骗0jsmith项目2jsmithSkipDispatch0jsmithSLAMiss0jsmith亚型0jsmith类型0jsmith空白1

您的设计是非规范化的，因为每个类别实际上应该在单独的行中。但是我们可以通过使用STRING_SPLIT用逗号分隔类别来转换它。

您可以简单地按用户和类别进行聚合。

SELECT
wf.[User],
s.value AS Category,
COUNT(*) Errors
FROM Tickets_SLA_Workflow wf
JOIN Tickets t ON wf.Tickets_RecID = t.Tickets_RecID
JOIN Tickets_User_Defined_Field_Value fv
ON t.Tickets_RecID = fv.Tickets_RecID
AND fv.User_Defined_Field_RecID = 28
CROSS APPLY STRING_SPLIT (fv.User_Defined_Field_Value, ',') s
WHERE wf.Date_Responded_UTC BETWEEN @Start AND @End
GROUP BY
wf.User,
s.value;

如果不是所有的类别都用逗号正确地分隔，但是您有一个实际的类别列表，那么您可以通过使用LIKE得到几乎相同的结果。缺点是重叠的类别(如Contact和Contacts)将返回两次。

SELECT
wf.[User],
c.Category,
COUNT(*) Errors
FROM Tickets_SLA_Workflow wf
JOIN Tickets t ON wf.Tickets_RecID = t.Tickets_RecID
JOIN Tickets_User_Defined_Field_Value fv
ON t.Tickets_RecID = fv.Tickets_RecID
AND fv.User_Defined_Field_RecID = 28
JOIN Categories c ON fv.User_Defined_Field_Value LIKE '%' + c.Category + '%'
WHERE wf.Date_Responded_UTC BETWEEN @Start AND @End
GROUP BY
wf.User,
c.Category;

注意:避免使用没有意义的别名，如A和B

注意:表上的LEFT JOIN后面跟着WHERE变成了INNER JOIN