如何在mysql的父子类别下计算总数 [英] How to count the total items under the parent sub category in mysql

问题描述

我下面有两个表

subcategorytbl

subcategorytbl

 sub_id    sub_title     sub_parent_id
   1        For rent           0
   2        Car                1
   3        Motorcycle         1 
   4        For Sale           0 
   5        Boat               4

itemtbl

  item_id    sub_id
    1          2
    2          2  
    3          3
    4          1
    5          2
    6          5

汽车和摩托车属于出租"类别，而船属于待售"子类别.因此，结果应该是这样的:

Car and Motorcycle are under For rent and Boat is under For Sale subcategory. Therefore,the result should be like this:

For Rent(5) 
- Car(3)
- Motorcycle(1)
For Sale(1) 
- Boat(1)

以下是我的查询:

  SELECT  count(*) as itemcount ,  sub_parent_id  from 
   subcategorytbl
  LEFT JOIN  itemtbl ON   subcategorytbl.sub_id=itemtbl.sub_id  
  GROUP BY   subcategorytbl.sub_id

推荐答案

select concat(if(a.sub_parent_id>0," - ",""), a.sub_title,'(',count(itb.it_id),')') from subcategorytbl a inner join (select sub_id as sid,sub_id as chid from subcategorytbl where sub_parent_id=0 union
select sub_parent_id as sid,sub_id as chid from subcategorytbl where sub_parent_id>0
union select sub_id as sid,sub_id as chid from subcategorytbl where sub_parent_id>0
 ) b on b.sid=a.sub_id  
 inner join (select item_id as it_id,sub_id  as itsid from itemtbl) itb on itb.itsid=b.chid 
 group by a.sub_title order by a.sub_id;

输出

For rent(5)
 - Car(3)
 - Motorcycle (1)
For Sale(1)
 - Boat(1)

这篇关于如何在mysql的父子类别下计算总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！