SQL使用CASE WHEN提取字段后分组统计各分类行数的方法

要实现按提取出的应用名称分组统计记录条数，只需对计算得到的Application字段做分组，搭配COUNT(*)聚合函数统计每组的记录总数即可，以下是两种可直接运行的写法：

写法1：子查询预处理（推荐，可维护性更高） #

把应用名称提取逻辑放在子查询中预先计算，后续分组、过滤不需要重复编写相同的CASE表达式，调整提取规则时只需要修改一处代码：

SELECT 
    [Application],
    COUNT(*) AS [Counts]
FROM (
    SELECT 
        CASE 
            WHEN w.[description] LIKE '%Provisioning%' 
            THEN LEFT(w.[description], CHARINDEX('Provisioning', w.[description]) - 2)
            ELSE '' 
        END AS [Application]
    FROM [Table 1] AS w
    LEFT JOIN [Table2] AS i ON i.[id] = w.[owner]
    WHERE 
        [owner_name] IN ('Specific_Owner')
        AND (CONVERT(DATETIME, DATEADD(s, w.[created]/1000, '1970-01-01'), 110) BETWEEN CONVERT(DATETIME, '2022-05-09') AND CONVERT(DATETIME, '2022-06-01'))
        AND w.[description] NOT LIKE '%Approve%'
) AS pre_processed
GROUP BY [Application]
-- 若需要按记录数从高到低排序，可追加下方排序语句
-- ORDER BY [Counts] DESC

写法2：无嵌套直接分组 #

不需要嵌套子查询，直接把CASE表达式写在GROUP BY子句中即可，适合逻辑简单不会频繁调整的场景：

SELECT 
    CASE 
        WHEN w.[description] LIKE '%Provisioning%' 
        THEN LEFT(w.[description], CHARINDEX('Provisioning', w.[description]) - 2)
        ELSE '' 
    END AS [Application],
    COUNT(*) AS [Counts]
FROM [Table 1] AS w
LEFT JOIN [Table2] AS i ON i.[id] = w.[owner]
WHERE 
    [owner_name] IN ('Specific_Owner')
    AND (CONVERT(DATETIME, DATEADD(s, w.[created]/1000, '1970-01-01'), 110) BETWEEN CONVERT(DATETIME, '2022-05-09') AND CONVERT(DATETIME, '2022-06-01'))
    AND w.[description] NOT LIKE '%Approve%'
GROUP BY 
    CASE 
        WHEN w.[description] LIKE '%Provisioning%' 
        THEN LEFT(w.[description], CHARINDEX('Provisioning', w.[description]) - 2)
        ELSE '' 
    END

补充说明：如果不需要统计未匹配到Provisioning关键字、Application值为空字符串的分组，直接在查询中增加对应过滤条件即可。

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