Mysql通过内连接实现递归查询生成树状

柔情只为你懂 2024-04-19 13:01 65阅读 0赞

偶然间组长说要求我用Mysql查询实现一下某个菜单表的树状结果图

网上的某些递归查询对于我这种新手来说未免有点过于复杂，最后巧妙的使用了内连接解决递归查询生成树状的问题。

1.生成测试表：

create table treeNodes
     (
        id int primary key,
        nodename varchar(20),
        pid int
    );

2.添加测试数据：

![2019091816230988.png][]

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

查询结果：

![20190918162454803.png][]

#通过父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

查询结果：

![20190918162641569.png][]

此处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层子类![20190918163932352.png][]

4层子类![20190918163944707.png][]

[2019091816230988.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/45185fc3cc8a4393ac860cefca8bab5d.png
[20190918162454803.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/a8214f61a75d4ab2be82bfda7115bc27.png
[20190918162641569.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d3b5cec98a74482e84f0f9b94b29440d.png
[20190918163932352.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7de0a72f42d946daba7ee357d1dddbc0.png
[20190918163944707.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7d2b4da91a3c4db7a21bc37a5318885b.png