================================
©Copyright 蕃薯耀 2021-11-27
一、查询父菜单下所有的子菜单
#查询某个父菜单下所有的子菜单,包含该父菜单
#注意:查询有变量,直接在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