使用 mysql 查询查找所有嵌套子类别的最不密集的方法
我有一个数据库，其中的项目被分类。其中一些类别是嵌套的，例如：
Animals > Birds > Parrots
Animals > Birds > Penguin
Animals > Mammals > Cats
Animals > Mammals > Dogs
Animals > Reptiles > Snakes
Plants > Trees
Plants > Flowers
等
我将这些类别放在表格中，沿着
CATEGORY PARENT
Animals -
Birds Animals
Penguin Birds
等
的行我希望能够以动物为起点并列出该类别下的所有子类别，因此对于动物，我们会列出鸟类、哺乳动物、爬行动物、鹦鹉、企鹅、猫、狗、蛇
这可以通过单个查询实现吗？如果没有，我需要做什么
TIA
I have a database in which items are placed into categories. Some of these categories are nested, so as an example:
Animals > Birds > Parrots
Animals > Birds > Penguin
Animals > Mammals > Cats
Animals > Mammals > Dogs
Animals > Reptiles > Snakes
Plants > Trees
Plants > Flowers
etc
I have these in a table along the lines of
CATEGORY PARENT
Animals -
Birds Animals
Penguin Birds
etc
I'd like to be able to take the starting point of say, Animals and list all of the subcategories that come under that, so for animals we would have listed Birds, Mammals, Reptiles, Parrots, Penguin, Cats, DOgs, Snakes
Is this possible with a single query? If not, what would I need to do
TIA
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以摆脱递归并使用单个查询来查找子计数、直接类别和子类别，方法是按照下面的“修改的预序树遍历”文章，使用单个 SQL 查询：
google.com 中搜索关键字“Modified Tree Traversal + Sitepoint”，阅读有关 sitepoint 的文章。 com 解释了相同的模式。
You can get away of recursion and use single queries to find child count, immediate categories and subcategories within using single SQL query by following the "Modified Preorder Tree Traversal" article below:
Search google.com for the keywords "Modified Tree Traversal + Sitepoint" to read the an article on sitepoint.com that explains the same pattern.
如果您向架构添加一些层次结构元数据，则可以通过单个查询高效地完成此操作。
将两个整数列（
start和end）添加到您的类别表中。然后对树进行深度优先遍历，在每一步递增一个计数器，并在进入节点时将计数器值分配给start，在离开节点时将计数器值分配给end（即，当所有它的孩子已被处理）。因此，对于您的示例，值显示为
(start,end)：现在要选择 get Animals 及其子项，您可以运行如下查询：
您显然已经权衡了重建元数据的成本当层次结构发生变化而影响读取查询的效率时。对于相对静态的层次结构，这种技术效果相当好。
It is possible and efficient to do this with a single query if you add some hierarchy metadata to your schema.
Add two integer columns (
startandend) to your category table. Then do a depth-first traversal of your tree incrementing a counter at each step and assigning the counter value tostartwhen entering a node and toendwhen leaving (i.e. when all it's children have been processed).So for your example, with the values shown as
(start,end):Now to select get Animals and it's children you can just run something like this query:
You obviously have weigh the cost of rebuilding the metadata when the hierarchy changes against the efficiency for read queries. For relatively static hierarchies this technique works fairly well.
众所周知，SQL 不擅长遍历分层数据。
我会对此进行编程并递归或迭代地查找所有孩子。
下面的伪代码应该可以工作（如果数据中没有循环）
如果层次结构中存在循环，则必须确保不要追加已经存在于数组中的子级，否则循环将消耗所有内存并崩溃
SQL is notoriously bad at traversing hierarchical data.
I would program this and recursively or iteratively find all children.
the following pseudo code should work (if there are no loops in the data)
If there are loops in the hierarchy you must make sure you do not append children which are already in the array or the loop will consume all memory and crash
我对获取数据的 mysql 程序不太了解，但我们可以在 PHP 的帮助下使用单个 mysql 查询获取嵌套数据，您可以对任何语言使用相同的逻辑。
http://www.maheshchari.com/php-nested-类别-with-single-mysql-query/
I don't know much about mysql procedures to get the data but we can get nested data using single mysql query with help of PHP, you can use same logic for any language.
http://www.maheshchari.com/php-nested-categories-with-single-mysql-query/