计算多级类别树中属于MYSQL中某个上层树的项目
因此,假设我有一个名为类别的表...它包含...嗯...类别
表类别包含字段“id”、“name”和“parentID”
Table Categories:
id name parentID
1 cat1 null
2 cat2 null
3 cat3 null
此外,它还包含作为上述类别的子类别的子类别。所以:
表类别:
id name parentID
1 cat1 null
2 cat2 null
3 cat3 null
4 cat4 1
5 cat5 1
6 cat6 2
7 cat7 2
8 cat8 3
9 cat9 3
假设我们有属于类别的项目......所以我们有表项目:
id name catID
1 boo 9
2 kya 8
3 muwa 6
4 haha 7
我想计算属于某个类别(包括其子类别)的项目数量。因此,如果我想计算属于 cat3 的项目,它应该返回 2 个项目,因为 boo 和 kyaa 属于类别 9 和 8,而类别 9 和 8 又是 cat3 的子类别...
您会建议我执行什么 mysql 查询来实现这个?
So suppose I have a table named categories...it contains...well...categories
table categories contain fields 'id', 'name' and 'parentID'
Table Categories:
id name parentID
1 cat1 null
2 cat2 null
3 cat3 null
Further it also contains subcategories that are children categories of the categories above. So:
Table Categories:
id name parentID
1 cat1 null
2 cat2 null
3 cat3 null
4 cat4 1
5 cat5 1
6 cat6 2
7 cat7 2
8 cat8 3
9 cat9 3
And suppose we have items that belongs to categories.....so we have the table item:
id name catID
1 boo 9
2 kya 8
3 muwa 6
4 haha 7
and I want to count the number of items that belongs to a certain category including its subcategories. So if I wanna count items that belong to cat3, it should return 2 items since boo and kyaa belong to category 9 and 8 which in turn are subcategories of cat3...
what mysql query would you recommend me execute to go about implementing this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
http://dev.mysql.com/tech-resources/articles/hierarchical -data.html
@jakoch 的注释:之前的链接不再有效。
有关替代说明,请参阅:
http://mikehillyer.com/articles/managing-hierarchical-data-in- mysql/
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Note by @jakoch: The previous link is not valid anymore.
For an alternate description, see:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/