如何用SQLite统计指定字符在另一列子串中的出现次数？

要实现统计color表中每个字符在deck表code列的出现次数（包含计数为0的字符），你可以通过动态生成模糊匹配条件替换硬编码字符，以下是可行的SQL方案：

方案一：使用交叉连接（CROSS JOIN） #

SELECT 
    color.name AS Color,
    SUM(CASE WHEN deck.code LIKE '%' || color.name || '%' THEN 1 ELSE 0 END) AS Count
FROM color
CROSS JOIN deck
GROUP BY color.name
ORDER BY Count, Color;

说明 #

• 用'%' || color.name || '%'动态拼接匹配模式，替代你之前硬编码的'%W%'，让每个color表中的字符自动生成对应的模糊查询条件。
• CROSS JOIN会将color表每条记录与deck表每条记录组合，CASE语句判断当前deck的code是否包含该字符，最后通过SUM统计符合条件的次数。即使某个字符在所有deck的code中都未出现，SUM结果也会是0，满足需求。

方案二：使用左连接（LEFT JOIN）（更高效） #

如果deck表数据量较大，左连接可减少不必要的组合，提升查询效率：

SELECT 
    c.name AS Color,
    COALESCE(SUM(CASE WHEN d.code LIKE '%' || c.name || '%' THEN 1 ELSE 0 END), 0) AS Count
FROM color c
LEFT JOIN deck d ON d.code LIKE '%' || c.name || '%'
GROUP BY c.name
ORDER BY Count, Color;

说明 #

• LEFT JOIN确保color表中的所有字符都会被保留，即使没有任何deck的code包含该字符。
• COALESCE函数用于处理SUM返回NULL的情况（无匹配deck记录时），将其转换为0，保证结果一致性。

这两个方案都能生成你期望的结果表，包含所有color字符及其对应计数。

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