mysql - mysql中如何统计父子类下的总项目数

我下面有两张表

子类别表

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

项目表

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

汽车和摩托车在出租子类别下，船在待售子类别下。因此，结果应该是这样的:

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 - mysql中如何统计父子类下的总项目数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37021147/