递归类别下的项目数

Question

I have two table. Tables name of categories and items. I want to list category with item count.

categories table:

id | parent | catname | catpath
1    0        A         1
2    0        B         2
3    1        A1        1,3
4    3        A11       1,3,4
5    2        B1        2,5
6    0        C         6

....

items table:

id | catid | title
1    1       title1
2    1       title2
3    3       title3
4    4       title4
5    4       title5
6    3       title6
7    5       title7

And I want print categories like this: print catname (itemcount = own item count + sub category's item count)

A (4)   /*<- sub category's item count + own item count*/
   A1 (1)
   A11 (2)
B (1)
   B1 (1)

UPDATE: SQLFIDDLE

Answer 1

You can do this because you have the full path in the categories table. Kudos. So, you don't need a recursive query:

select c.catname, count(ic.catid) as cnt
from categories c left outer join
     (select i.*, c.catname, c.catpath
      from items i join
           categories c
           on i.catid = c.id
     ) as ic
     on concat(ic.catpath, ',') like concat(c.catpath, ',%')
group by c.catname;

This uses like to traverse the hierarchy. The reason for the , is so 1 doesn't match 10.