建议SQL查询从邻接列表模型检索所有叶节点 [英] Suggest SQL Query to retrieve all leaf nodes from Adjacency List Model

问题描述

我有一个表结构在mysql像这样：

I have got a table structure in mysql like this:

  ________________________
 |  id      | int(auto)   |     
 |------------------------|   
 |  name    | varchar     |
 |------------------------|
 |  link    | varchar     |
 |------------------------|
 | parent_id| int         |
 |________________________|

基本上它是一个数据库驱动菜单的设计，根菜单有 parent_id 0，而子菜单的根菜单在 parent_id 字段中指定。它可以高达3-4级。

Basically it is a design for a database driven menu, the root menu has parent_id 0, whereas child menu have their root menu specified on parent_id field. It can be up to 3-4 level.

现在，我需要在根菜单下找到所有子菜单。我可以轻松地做一个级菜单，但我不能在这后面。我可以这样做通过编写一个程序（通过合并结果集），但它将是更容易和方便的方式，如果我可以使用只是SQL。

Now, I need to find all child menu under a root menu. I can easily do that for one level menu, but I can't traverse behind that. I can do that by writing a program (by merging resultsets), but it will be much easier and convenient way, if I can do that using just SQL.

我无法更改表的任何结构或修改系统。请帮助。

I can't change any structure of the table or otherwise modify the system. Please help.

推荐答案

我使用下面的查询来获取所有的子菜单的id在这个查询中是2）。它可以支持多达3个级别。我知道查询没有优化和性能问题，但仍然我认为这是实现解决方案，没有使用过程（我不能使用）的唯一方法。

I have used the following query to get all the child menu id (comma separated) of a root menu (in this query it is 2). It can support upto 3 levels. I know the query is not optimized and got performance issue, but still I think it is the only way to achieve the solution, without using procedure(which I can't use).

select group_concat(lev0) from 
(select concat_ws(',',lev2,group_concat(lev3)) as lev0 from 
(SELECT  t2.id as lev2, 
t3.id as lev3, 
t4.id as lev4
FROM menus AS t1
LEFT JOIN menus AS t2 ON t2.parent_id = t1.id
LEFT JOIN menus AS t3 ON t3.parent_id = t2.id
LEFT JOIN menus AS t4 ON t4.parent_id = t3.id
WHERE t1.id = '2') t0 group by lev2) t;

这篇关于建议SQL查询从邻接列表模型检索所有叶节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！