查找树中所有非空类别
我有一个类别表设置为 [id，name，parent_id] 和一个项目表 [id，name，category_id，visible]。我想做的是创建一个查询，该查询将返回所有非空类别的所有 id，非空是因为它或其子级之一至少有一个属于它的项目。在 MySQL 中执行此操作的最佳方法是什么？
编辑
SELECT DISTINCT Category_id FROM Items
这适用于包含项目的类别，但我还需要包含类别的所有项目的父类别。该查询将与其他一些过滤器一起用作子查询。
顶级类别
-> 二级类别
--> 三级类别
---> 第 1 项
---> 第 2 项
I have a categories table set up as so [id, name, parent_id] and a items table [id, name, category_id, visible]. What I'm trying to do is create a query that will return all the ids of all non-empty categories, non empty being that it or one of it's children has at least one item belonging to it. What would be the best way to do this in MySQL?
edit
SELECT DISTINCT category_id FROM Items
This works for categories containing items, but I also need the parent categories of all item containing categories. This query will be used as a subquery along with some other filters.
Top Level Category
->Second Level Category
-->Third Level Category
--->Item 1
--->Item 2
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
也许偏离主题，但我认为它仍然值得参考：有关管理的广泛文章MySQL 中的分层数据。
Maybe off topic, but I think it is still worth referencing: Extensive Article on Managing Hierarchical Data in MySQL.
所有非空类别（只有那些）都有
category_id指向它们的项目，因此您可以从项目表中选择category_id：据我所知，您无法在一个查询中选择这些类别的所有祖先，但是您可能想要使用另一种树模型。
使用嵌套集模型，您的查询可能如下所示：
我不确定它是否有效，但你可以尝试一下。
All non-empty categories, and only those, have items with
category_idpointing at them, therefore you could just selectcategory_ids from items table:As far as I know, you can't select all the ancestors of these categories in one query, however you might want to use another tree model.
With the nested set model, your query could look like this:
I'm not sure if it'll work, but you can try.