SQL查询：统计table2的name在table1出现次数并按cat分组求和

正确SQL实现 #

方案1：子查询嵌套写法（兼容所有主流数据库） #

SELECT 
  cat,
  SUM(occurrence_count) AS count
FROM (
  SELECT 
    cat,
    ROUND(
      (SUM(CHAR_LENGTH(t1.mystring)) - SUM(CHAR_LENGTH(REPLACE(t1.mystring, t2.name, '')))) 
      / CHAR_LENGTH(t2.name)
    ) AS occurrence_count
  FROM table2 t2, table1 t1
  GROUP BY cat, name
  HAVING occurrence_count > 0
) AS temp
GROUP BY cat;

方案2：CTE写法（适配MySQL8.0+、PostgreSQL、Oracle等现代数据库） #

WITH name_occurrence AS (
  SELECT 
    cat,
    ROUND(
      (SUM(CHAR_LENGTH(t1.mystring)) - SUM(CHAR_LENGTH(REPLACE(t1.mystring, t2.name, '')))) 
      / CHAR_LENGTH(t2.name)
    ) AS cnt
  FROM table2 t2
  CROSS JOIN table1 t1
  GROUP BY cat, name
  HAVING cnt > 0
)
SELECT cat, SUM(cnt) AS count
FROM name_occurrence
GROUP BY cat;

逻辑说明 #

你原来的SQL已经正确计算出了单个name在table1中的出现次数，只需要在此基础上做二次聚合即可：

• 内层保留原有的name+cat分组逻辑，避免直接修改外层分组导致的计算偏差
• 外层对内层的单name计数结果按cat分组求和，就能得到每个分类下所有name的总出现次数
• 这里把原来的子查询计数改为关联表的写法，逻辑更清晰，执行效率也更高

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