mysql - 统计单个和多个类别的记录

Question

我的表里有这些记录

researchID | category
      19        math
      20        math
      21        math
      22        math
      22        history
      23        math
      23        english
      24        math
      24        english

使用此查询:SELECT researchID, GROUP_CONCAT(DISTINCT category_id SEPARATOR "|") as category FROM tbl_submission_categories GROUP BY researchid。我的结果是这样的

 researchID | category
      19        math
      20        math
      21        math
      22        math|history
      23        math|english
      24        math|english

但是我想要的结果是这样

research_count | category
      3           math
      1           math|history
      2           math|english

我需要什么 sql 查询？

Answer 1

您可以通过将查询移动到内部子句中并对内部查询的结果集应用聚合来实现此目的

SELECT category, COUNT(*) research_count 
FROM(
    SELECT researchID, GROUP_CONCAT(DISTINCT category_id SEPARATOR "|") as category 
    FROM tbl_submission_categories 
    GROUP BY researchid
) t
GROUP BY category

Demo