mysql - 如何修改 MySQL 查询来计算某个值在数据库表/结果中出现的次数？

Question

以下数据库结果/表是通过使用下面的 SQL 查询将 2 个表连接在一起创建的。我还想做的是显示第三列，该列计算某个值包含在 main_category 列中的次数。底部表格中可以看到所需结果的示例，其中第三列显示“主类别 1”出现的次数 - 在本示例中为 8 次，“主类别 2”出现 6 次。有谁知道我需要对现有查询做什么才能做到这一点？是否涉及到GROUP BY和COUNT？

+--------------+-----------------+  | sub_category | main_category   |  +--------------+-----------------+  | SubCategoryA | main category 1 |  | SubCategoryB | main category 1 |  | SubCategoryC | main category 1 |  | SubCategoryD | main category 1 |  | SubCategoryE | main category 1 |  | SubCategoryF | main category 1 |  | SubCategoryG | main category 1 |  | SubCategoryH | main category 1 |  | SubCategoryI | main category 2 |  | SubCategoryJ | main category 2 |  | SubCategoryK | main category 2 |  | SubCategoryL | main category 2 |  | SubCategoryM | main category 2 |  | SubCategoryN | main category 2 |  +--------------+-----------------+  

  SELECT headings.heading AS sub_category,    
         primary_category AS main_category  
  FROM   TABLE1 INNER JOIN headings ON TABLE1.Heading=TABLE2.heading   

UNION

  SELECT TABLE2.heading     AS heading,
         secondary_category AS main_category   
  FROM   TABLE1 INNER JOIN TABLE2 ON TABLE1.Heading=TABLE2.heading   

ORDER BY main_category ASC

+--------------+-----------------+-------+  | sub_category | main_category   | count |  +--------------+-----------------+-------+  | SubCategoryA | main category 1 |     8 |  | SubCategoryB | main category 1 |     8 |  | SubCategoryC | main category 1 |     8 |  | SubCategoryD | main category 1 |     8 |  | SubCategoryE | main category 1 |     8 |  | SubCategoryF | main category 1 |     8 |  | SubCategoryG | main category 1 |     8 |  | SubCategoryH | main category 1 |     8 |  | SubCategoryI | main category 2 |     6 |  | SubCategoryJ | main category 2 |     6 |  | SubCategoryK | main category 2 |     6 |  | SubCategoryL | main category 2 |     6 |  | SubCategoryM | main category 2 |     6 |  | SubCategoryN | main category 2 |     6 |  +--------------+-----------------+-------+  

Answer 1

我在您的问题中提供了子查询中提供的示例数据，只是为了向您展示如何做到这一点的逻辑

SELECT *,(SELECT COUNT( mcat) FROM `categoriesstack`

 WHERE mcat=cs.mcat) AS `count` FROM `categoriesstack` cs 

Here is your fiddle

这是您在查询中实现的方法，但不确定是否有效

  SELECT headings.heading AS sub_category,    
         primary_category AS main_category,
  (SELECT COUNT(primary_category) FROM `TABLE1` WHERE primary_category=TABLE1 .primary_category) AS `count`   
  FROM   TABLE1 INNER JOIN headings ON TABLE1.Heading=TABLE2.heading   

UNION

  SELECT TABLE2.heading     AS heading,
         secondary_category AS main_category,
  (SELECT COUNT(secondary_category) FROM `TABLE1` WHERE secondary_category=TABLE1 .secondary_category) AS `count`   
  FROM   TABLE1 INNER JOIN TABLE2 ON TABLE1.Heading=TABLE2.heading   

ORDER BY main_category ASC

希望一切顺利