SQL-递归查询

最新推荐文章于 2025-04-10 16:33:25 发布

与光同行_阿贤

最新推荐文章于 2025-04-10 16:33:25 发布

阅读量380

点赞数 4

分类专栏：数据库开发文章标签： sql 数据库

本文链接：https://blog.youkuaiyun.com/Sheng_GuoFu/article/details/139887305

版权

数据库开发专栏收录该内容

3 篇文章

订阅专栏

查询：指定菜单下，所有子菜单下的子菜单... 列表

1、准备表、数据

-- 01、创建[菜单信息表]
create table MenuInfo
(
    id        int identity
        constraint MenuInfo_pk
            primary key,
    menu_name varchar(40) default '',
    parent_id int         default ''
)
go

-- 02、写入测试数据
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级菜单',NULL)

insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-1',1)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-2',1)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-3',1)

insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-1-三级菜单-1',2)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-1-三级菜单-2',2)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-1-三级菜单-3',2)

insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-2-三级菜单-1',3)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-2-三级菜单-2',3)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-2-三级菜单-3',3)

insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-3-三级菜单-1',4)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-3-三级菜单-2',4)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-3-三级菜单-3',4)

insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-3-三级菜单-1-四级菜单-1',11)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-3-三级菜单-1-四级菜单-2',11)
insert into dbo.MenuInfo(menu_name, parent_id) values ('一级-二级菜单-3-三级菜单-1-四级菜单-3',11)

2、递归查询

WITH Con(id, menu_name, parent_id, le) AS
(
    -- 初始数据
    SELECT id, menu_name, parent_id, le = 1
    FROM MenuInfo
    WHERE id = 4

    UNION ALL

    -- 递归成员
    SELECT a.id, a.menu_name, a.parent_id, le = b.le + 1
    FROM MenuInfo a
    INNER JOIN Con b ON a.parent_id = b.id
)
SELECT id, menu_name, parent_id, le
FROM Con;