MySQL分组后统计指定列出现次数的实现方案问询

你现在的问题是，原来的COUNT(mh.slug)统计的是原始关联表中mh.slug的总出现次数，但你需要的是每个subSlug（即mh.slug）在最终分组结果里的行数——比如washers-dryers对应4条分组记录，qty就应该显示4。下面是具体的修改方案：

修改后的查询语句 #

SELECT 
    mh.super_category AS parentDescription, 
    mh.super_category AS parentSlug, 
    mh.description AS subDescription, 
    mh.slug AS subSlug, 
    COUNT(*) OVER (PARTITION BY mh.slug) AS qty, 
    cg.Description AS nestedSubDescription, 
    cg.slug AS nestedSubSlug 
FROM Categories AS c 
INNER JOIN Category_Groups AS cg ON (c.Category_Group=cg.CategoryCode) 
INNER JOIN menu_heading AS mh ON (cg.MenuHeading = mh.id) 
INNER JOIN tbl_store_brands ON (tbl_store_brands.categoryID = c.pkID) 
WHERE c.seq <> 127 
GROUP BY cg.slug 
ORDER BY mh.super_category, mh.seq ASC, cg.seq, c.seq, c.Category_Group, c.Category_ID

关键修改说明 #

把原来的COUNT(mh.slug)替换成COUNT(*) OVER (PARTITION BY mh.slug)：

• PARTITION BY mh.slug会将整个结果集按subSlug拆分，对每个拆分后的组统计行数；
• 窗口函数OVER()不会改变你原本按cg.slug分组的逻辑，只是在每一行里附加对应的subSlug分组行数，完美匹配你要的效果。

如果你的数据库不支持窗口函数（比如老版本MySQL），可以用子查询替代：

SELECT 
    mh.super_category AS parentDescription, 
    mh.super_category AS parentSlug, 
    mh.description AS subDescription, 
    mh.slug AS subSlug, 
    (SELECT COUNT(*) 
     FROM Category_Groups cg_sub 
     INNER JOIN menu_heading mh_sub ON cg_sub.MenuHeading = mh_sub.id 
     WHERE mh_sub.slug = mh.slug 
       AND EXISTS (
           SELECT 1 
           FROM Categories c_sub 
           INNER JOIN tbl_store_brands tsb_sub ON tsb_sub.categoryID = c_sub.pkID 
           WHERE c_sub.Category_Group = cg_sub.CategoryCode 
             AND c_sub.seq <> 127
       )) AS qty, 
    cg.Description AS nestedSubDescription, 
    cg.slug AS nestedSubSlug 
FROM Categories AS c 
INNER JOIN Category_Groups AS cg ON (c.Category_Group=cg.CategoryCode) 
INNER JOIN menu_heading AS mh ON (cg.MenuHeading = mh.id) 
INNER JOIN tbl_store_brands ON (tbl_store_brands.categoryID = c.pkID) 
WHERE c.seq <> 127 
GROUP BY cg.slug 
ORDER BY mh.super_category, mh.seq ASC, cg.seq, c.seq, c.Category_Group, c.Category_ID

修改后，washers-dryers对应的4条记录里qty都会显示4，refrigerators对应的8条记录里qty都会显示8，和你期望的结果逻辑完全一致。

内容的提问来源于stack exchange，提问作者I-CRE8