Mysql 查询父菜单下所有的子菜单，Mysql迭代（递归）查询 |
您所在的位置：网站首页 › mysql递归查询sql › Mysql 查询父菜单下所有的子菜单，Mysql迭代（递归）查询 |
Mysql 查询父菜单下所有的子菜单，Mysql迭代（递归）查询
|
================================ ©Copyright 蕃薯耀 2021-11-27 https://www.cnblogs.com/fanshuyao/ 一、查询父菜单下所有的子菜单 #查询某个父菜单下所有的子菜单，包含该父菜单 #注意：查询有变量，直接在sql查询窗口在第二次查询数据会变少 #参考：https://blog.csdn.net/qq_37493556/article/details/108296542 SELECT ID.LEVEL, #_ids, DATA.* FROM ( SELECT @ids AS _ids, ( SELECT @ids := GROUP_CONCAT(id) FROM sys_menu WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids, @l := @l + 1 AS LEVEL FROM sys_menu, ( SELECT @ids := 3, @l := 0 ) b #@ids为输入的参数 WHERE @ids IS NOT NULL ) ID, sys_menu DATA WHERE FIND_IN_SET( DATA.id, ID._ids) ORDER BY LEVEL,id;
二、查询某个子菜单所有的[父]菜单（包含该子菜单） -- 查询某个子菜单所有的[父]菜单（包含该子菜单） SELECT id,LEVEL,DATA.`title`,DATA.`parent_id` FROM( SELECT @id AS _id, ( SELECT @id := parent_id FROM sys_menu WHERE id = @id) AS _pid, @l := @l+1 AS LEVEL FROM sys_menu, (SELECT @id := (15), @l := 0 ) b -- @id为输入的参数 WHERE @id > 0 ) ID, sys_menu DATA -- 表关联 WHERE ID._id = DATA.id ORDER BY LEVEL DESC
三、数据结构 -- 菜单 CREATE TABLE sys_menu( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键', parent_id BIGINT COMMENT '父菜单id', title VARCHAR(50) NOT NULL COMMENT '菜单标题', icon VARCHAR(100) NOT NULL COMMENT '菜单目录图标class', `type` SMALLINT NOT NULL DEFAULT 1 COMMENT '类型，0表示菜单目录，1表示链接菜单', open_type VARCHAR(50) COMMENT '打开类型，目录是空，菜单是：_iframe', href VARCHAR(2000) COMMENT '菜单点击链接', order_by DOUBLE NOT NULL DEFAULT 100 COMMENT '排序，越小越前，每个目录菜单最好相差为10', delete_flag SMALLINT NOT NULL DEFAULT 0 COMMENT '是否删除，默认为0表示未删除，1表示删除', create_time DATETIME NOT NULL DEFAULT NOW() COMMENT '创建时间', create_user BIGINT NOT NULL COMMENT '创建人', update_time DATETIME COMMENT '更新时间', update_user BIGINT COMMENT '更新人', remark VARCHAR(200) COMMENT '备注' ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4; INSERT INTO sys_menu(title,icon,open_type,href,create_user) VALUES ('菜单1','layui-icon layui-icon-set-fill', NULL, NULL, 1); INSERT INTO sys_menu(title,icon,open_type,href,create_user) VALUES ('菜单2','layui-icon layui-icon-set-fill', NULL, NULL, 1); INSERT INTO sys_menu(title,icon,open_type,href,create_user) VALUES ('菜单3','layui-icon layui-icon-set-fill', NULL, NULL, 1); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单1.1','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,1); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单1.2','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,1); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单1.3','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,1); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单1.4','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,1); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单2.1','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,2); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单2.2','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,2); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单2.3','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,2); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单3.1','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,3); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单3.2','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,3); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单3.3','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,3); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单3.3.3','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,13); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单3.3.4','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,13); INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id) VALUES ('菜单3.3.5','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,13); SELECT * FROM sys_menu l ORDER BY l.`order_by` ASC,l.`id` ASC;
（时间宝贵，分享不易，捐赠回馈，^_^）
================================ ©Copyright 蕃薯耀 2021-11-27 https://www.cnblogs.com/fanshuyao/
|
【本文地址】
今日新闻 |
推荐新闻 |