偶然间组长说要求我用Mysql查询实现一下某个菜单表的树状结果图
网上的某些递归查询对于我这种新手来说未免有点过于复杂,最后巧妙的使用了内连接解决递归查询生成树状的问题。
1.生成测试表:
create table treeNodes
(
id int primary key,
nodename varchar(20),
pid int
);
2.添加测试数据:
3.Sql语句:
#通过子ID获得父类ID
select distinct e.id,e.nodename,e.pid
from treeNodes a
inner join treeNodes b on b.id=a.pid or a.id=b.id
inner join treeNodes c on c.id=b.pid or b.id=c.id
inner join treeNodes e on e.id=c.pid or c.id=e.id
where a.id=17
查询结果:
#通过父ID获得子类ID
select distinct e.id,e.nodename,e.pid
from treeNodes a
inner join treeNodes b on a.id=b.pid or a.id=b.id
inner join treeNodes c on b.id=c.pid or b.id=c.id
inner join treeNodes e on c.id=e.pid or c.id=e.id
where a.id=1
查询结果:
此处SQL语句只支持从父菜单向下推3层子菜单栏查询,如果想要查询更多子菜单栏自行添加 inner join 的个数。
此处递归查询生成树状的缺点是无法确定查询子层的个数,如果查询的inner join 少于所有子层的个数,可能
会导致查询不出所有的子层,形成非完全树状。(当然,如果按照一般项目菜单栏子层个数来说,其实最多也就是4,5层而已)
3层/4层的比较:
#通过子ID获得父类ID--3层子类
select distinct e.id,e.nodename,e.pid
from treeNodes a
inner join treeNodes b on b.id=a.pid or a.id=b.id
inner join treeNodes c on c.id=b.pid or b.id=c.id
inner join treeNodes e on e.id=c.pid or c.id=e.id
where a.id=17
#通过子ID获得父类ID--4层子类
select distinct e.id,e.nodename,e.pid
from treeNodes a
inner join treeNodes b on b.id=a.pid or a.id=b.id
inner join treeNodes c on c.id=b.pid or b.id=c.id
inner join treeNodes d on d.id=c.pid or d.id=c.id
inner join treeNodes e on e.id=d.pid or d.id=e.id
where a.id=17
3层子类
4层子类