如何在单条SQLite查询中统计匹配多分类的行数？

这确实是个典型的痛点——当分类用空格分隔的字符串存储时，常规的CASE分组只会给每行分配一个分类，完全没法处理一行多分类的场景。我给你两个更简洁且符合DRY原则的解决方案：

方案1：用CTE生成分类列表+左连接统计 #

先通过CTE（公共表表达式）定义所有需要统计的分类（包括none），再左连接原表进行匹配统计，这样不用重复写一堆UNION：

WITH categories_list AS (
    SELECT 'a' AS cat UNION ALL
    SELECT 'b' AS cat UNION ALL
    SELECT 'c' AS cat UNION ALL
    SELECT 'none' AS cat
)
SELECT 
    cl.cat AS categories,
    COUNT(CASE
        WHEN cl.cat = 'none' THEN t.id  -- 统计空分类的行
        ELSE t.id                      -- 统计匹配到对应分类的行
    END) AS total
FROM categories_list cl
LEFT JOIN test t ON 
    -- 非none分类：匹配包含该分类的行（注意加空格避免部分匹配）
    (cl.cat != 'none' AND ' ' || t.cats || ' ' LIKE '% ' || cl.cat || ' %')
    -- none分类：匹配cats为空或null的行
    OR (cl.cat = 'none' AND (t.cats IS NULL OR t.cats = ''))
GROUP BY cl.cat;

这里加' ' || t.cats || ' '是为了避免类似分类'a'匹配到'aa'的情况，让匹配更精准。

方案2：利用字符串拆分函数（SQLite 3.31.0+适用） #

如果你的SQLite版本在3.31.0及以上，可以用STRING_SPLIT函数把分隔的分类拆成单独的行，再统一统计：

WITH split_categories AS (
    -- 拆分非空的分类行
    SELECT 
        id,
        TRIM(value) AS cat
    FROM test
    LEFT JOIN STRING_SPLIT(cats, ' ') ON 1=1
    WHERE TRIM(value) != ''
    
    UNION ALL
    
    -- 单独处理空分类的情况
    SELECT 
        id,
        'none' AS cat
    FROM test
    WHERE cats IS NULL OR cats = ''
)
SELECT 
    cat AS categories,
    COUNT(*) AS total  -- 每个拆分后的行对应一个分类+原行，直接统计即可
FROM split_categories
GROUP BY cat;

这个方案更灵活，后续如果新增分类，只需要确保拆分逻辑正确就行，不用修改分类列表。

两种方案都能得到你想要的结果：每个分类（包括none）的条目数，而且不会出现重复代码的问题。

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