<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
通過mysql 8.0以下版本實現,一個人多角色id,一個角色對應某個節點menu_id,根節點的父節點儲存為NULL, 向上遞迴查詢父節點並返回樹結構。如果只有葉子,剔除掉; 如果只有根,只顯示一個禿頂的根 ;如果既有葉子又有根則顯示葉子與根。如果 傳入角色ID 5,15,25,26,則只查詢5,15的所有父節點,因為25,26無根節點
需求:通過mysql 8.0以下版本實現,一個人多角色id,一個角色對應某個節點menu_id,根節點的父節點儲存為NULL, 向上遞迴查詢父節點並返回樹結構。
如果只有葉子,剔除掉; 如果只有根,只顯示一個禿頂的根 ;如果既有葉子又有根則顯示葉子與根。測試資料:
如果 傳入角色ID【auth_id】: 5,15,25,26,則只查詢5,15的所有父節點,因為25,26無根節點
測試資料:
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for Menu -- ---------------------------- DROP TABLE IF EXISTS `Menu`; CREATE TABLE `Menu` ( `menu_id` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '0', `sup_menu` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL, `auth_id` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`menu_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; -- ---------------------------- -- Records of Menu -- ---------------------------- BEGIN; INSERT INTO `Menu` VALUES ('1', NULL, '1'); INSERT INTO `Menu` VALUES ('11', NULL, '11'); INSERT INTO `Menu` VALUES ('12', '11', '12'); INSERT INTO `Menu` VALUES ('13', '11', '13'); INSERT INTO `Menu` VALUES ('14', '12', '14'); INSERT INTO `Menu` VALUES ('15', '12', '15'); INSERT INTO `Menu` VALUES ('16', '13', '16'); INSERT INTO `Menu` VALUES ('17', '13', '17'); INSERT INTO `Menu` VALUES ('2', '1', '2'); INSERT INTO `Menu` VALUES ('22', '21', '26'); INSERT INTO `Menu` VALUES ('25', '22', '25'); INSERT INTO `Menu` VALUES ('3', '1', '3'); INSERT INTO `Menu` VALUES ('4', '2', '4'); INSERT INTO `Menu` VALUES ('5', '2', '5'); INSERT INTO `Menu` VALUES ('6', '3', '6'); INSERT INTO `Menu` VALUES ('7', '3', '7'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
方法一:純儲存過程實現
-- 純儲存過程實現 DELIMITER // -- 如果只有葉子,剔除掉; 如果只有根,只顯示一個禿頂的根 ;如果既有葉子又有根則顯示 DROP PROCEDURE if EXISTS query_menu_by_authid; CREATE PROCEDURE query_menu_by_authid(IN roleIds varchar(1000)) BEGIN -- 用於判斷是否結束迴圈 declare done int default 0; -- 用於儲存結果集 declare menuid bigint; declare temp_menu_ids VARCHAR(3000); declare temp_sup_menus VARCHAR(3000); declare return_menu_ids VARCHAR(3000); -- 定義遊標 declare idCur cursor for select menu_id from Menu where FIND_IN_SET(auth_id,roleIds) ; -- 定義 設定迴圈結束標識done值怎麼改變 的邏輯 declare continue handler for not FOUND set done = 1; open idCur ; FETCH idCur INTO menuid; -- 臨時變數儲存menu_id集合 SET temp_menu_ids = ''; -- 返回儲存menu_id集合 SET return_menu_ids = ''; WHILE done<> 1 DO -- 只查詢 單個 auth_id 相關的menu_id -- 通過authid, 查詢出menu_id, sup_menu is null SELECT GROUP_CONCAT(T2._menu_id) as t_menu_id, GROUP_CONCAT(T2._sup_menu) as t_sup_menu into temp_menu_ids,temp_sup_menus FROM ( SELECT -- 儲存當前節點。(從葉節點往根節點找,@r 儲存當前到哪個位置了)。@r 初始為要找的節點。 -- _menu_id 當前節點 DISTINCT @r as _menu_id, ( SELECT CASE WHEN sup_menu IS NULL THEN @r:= 'NULL' ELSE @r:= sup_menu END FROM Menu WHERE _menu_id = Menu.menu_id ) AS _sup_menu, -- 儲存當前的Level @l := @l + 1 AS level FROM ( SELECT @r := menuid, @l := 0 ) vars, Menu AS temp -- 如果該節點沒有父節點,則會被置為0 WHERE @r <> 0 ORDER BY @l DESC ) T2 INNER JOIN Menu T1 ON T2._menu_id = T1.menu_id ORDER BY T2.level DESC ; -- 滿足必須要有根節點NULL字元,則表明有根,否則不拼接給返回值 IF FIND_IN_SET('NULL',temp_sup_menus) > 0 THEN SET return_menu_ids = CONCAT(temp_menu_ids,',',return_menu_ids); END IF; FETCH idCur INTO menuid; END WHILE; CLOSE idCur; -- 返回指定menu_id 的資料集合 select Menu.menu_id,Menu.sup_menu,Menu.auth_id FROM Menu WHERE FIND_IN_SET(menu_id,return_menu_ids) ORDER BY Menu.menu_id*1 ASC ; END; // DELIMITER; CALL query_menu_by_authid('5,15,25,26'); CALL query_menu_by_authid('5,17'); CALL query_menu_by_authid('5,11');
方法二:函數+儲存過程實現
-- 函數+儲存過程實現 -- 根據葉子節點查詢所有父節點及其本身節點。如果只有葉子,剔除掉; 如果只有根,只顯示一個禿頂的根 ;如果既有葉子又有根則顯示. DROP FUNCTION IF EXISTS `getParentList`; CREATE FUNCTION `getParentList`(in_menu_id varchar(255)) RETURNS varchar(3000) BEGIN DECLARE sTemp VARCHAR(3000); DECLARE sTempPar VARCHAR(3000); SET sTemp = ''; SET sTempPar = in_menu_id; -- 迴圈遞迴 WHILE sTempPar is not null DO -- 判斷是否是第一個,不加的話第一個會為空 IF sTemp != '' THEN SET sTemp = concat(sTemp,',',sTempPar); ELSE SET sTemp = sTempPar; END IF; SET sTemp = concat(sTemp,',',sTempPar); SELECT group_concat(sup_menu) INTO sTempPar FROM Menu where sup_menu<>menu_id and FIND_IN_SET(menu_id,sTempPar) > 0; END WHILE; RETURN sTemp; END; DELIMITER // -- 如果只有葉子,剔除掉; 如果只有根,只顯示一個禿頂的根 ;如果既有葉子又有根則顯示 DROP PROCEDURE if EXISTS select_menu_by_authids ; CREATE PROCEDURE select_menu_by_authids(IN roleIds varchar(3000)) BEGIN -- 用於判斷是否結束迴圈 declare done int default 0; -- 用於儲存結果集 declare menuid varchar(255); declare set_menu_ids VARCHAR(3000); -- 檢查是否單葉子節點 單葉子節點 sup_menu is not null -- sup_menu 是否為null declare _sup_menu int default -1; -- 定義遊標 declare idCur cursor for select menu_id from Menu where FIND_IN_SET(auth_id,roleIds) ; -- 定義 設定迴圈結束標識done值怎麼改變 的邏輯 declare continue handler for not FOUND set done = 1; OPEN idCur ; FETCH idCur INTO menuid; -- 臨時變數儲存menu_id集合 SET set_menu_ids = ''; WHILE done<> 1 DO SELECT sup_menu INTO _sup_menu FROM Menu WHERE FIND_IN_SET(menu_id,getParentList(menuid)) ORDER BY sup_menu ASC LIMIT 1; -- 查詢指定角色對應的menu_id ,sup_menu is null 則說明有根,則進行拼接 IF _sup_menu is NULL THEN SELECT CONCAT(set_menu_ids, GROUP_CONCAT(menu_id),',') INTO set_menu_ids FROM Menu where FIND_IN_SET(menu_id,getParentList(menuid)) ; END IF; FETCH idCur INTO menuid; END WHILE; CLOSE idCur; -- 返回指定menu_id 的資料集合 SELECT Menu.menu_id,Menu.sup_menu,Menu.auth_id FROM Menu WHERE FIND_IN_SET(menu_id,set_menu_ids) ORDER BY Menu.menu_id*1 ASC ; END ; // DELIMITER ; CALL select_menu_by_authids('5,15,25,26'); CALL select_menu_by_authids('5,17'); CALL select_menu_by_authids('5,11');
方法三:純函數實現
-- 根據葉子節點查詢所有父節點及其本身節點。如果只有葉子,剔除掉; 如果只有根,只顯示一個禿頂的根 ;如果既有葉子又有根則顯示. DROP FUNCTION IF EXISTS `getParentLists`; -- 引數1角色id 字串逗號隔開; 引數2 角色id 個數 CREATE FUNCTION `getParentLists`(in_roleIds varchar(1000),count_roleIds INT) RETURNS VARCHAR(3000) BEGIN -- 臨時存放通過單個角色查詢的單個menu_id DECLARE sMenu_id_by_roleId VARCHAR(1000); -- 臨時存放通過單個角色查詢的多個menu_id DECLARE sMenu_ids_by_roleId VARCHAR(1000); -- 臨時存放通過多個角色查詢的多個menu_id DECLARE sMenu_ids_by_roleIds VARCHAR(1000); -- 函數返回的menu_id 集合 DECLARE sReturn_menu_ids VARCHAR(3000); -- 當前角色 DECLARE current_roleId_rows INT DEFAULT 0; SET sMenu_id_by_roleId = ''; SET sMenu_ids_by_roleIds = ''; SET sReturn_menu_ids = ''; -- 迴圈多角色 WHILE current_roleId_rows < count_roleIds DO -- 依次按角色取1條menu_id SELECT menu_id INTO sMenu_id_by_roleId FROM Menu WHERE FIND_IN_SET(auth_id, in_roleIds) ORDER BY menu_id DESC LIMIT current_roleId_rows, 1 ; SET sMenu_ids_by_roleId = sMenu_id_by_roleId; WHILE sMenu_ids_by_roleId IS NOT NULL DO -- 判斷是否是第一個,不加的話第一個會為空 IF sMenu_ids_by_roleIds != '' THEN SET sMenu_ids_by_roleIds = CONCAT(sMenu_ids_by_roleIds,',',sMenu_ids_by_roleId); ELSE SET sMenu_ids_by_roleIds = sMenu_ids_by_roleId; END IF; -- 通過角色id 拼接 所有的父節點,重點拼接根節點,根節點置為字元NULL,用於後面判斷是否有根 SELECT GROUP_CONCAT( CASE WHEN sup_menu IS NULL THEN 'NULL' ELSE sup_menu END ) INTO sMenu_ids_by_roleId FROM Menu WHERE FIND_IN_SET(menu_id,sMenu_ids_by_roleId) > 0; END WHILE; SET current_roleId_rows=current_roleId_rows+1; -- 滿足必須要有根節點NULL字元,則表明有根,否則不拼接給返回值 IF FIND_IN_SET('NULL',sMenu_ids_by_roleIds) > 0 THEN SET sReturn_menu_ids = CONCAT(sReturn_menu_ids,',',sMenu_ids_by_roleIds); END IF; -- 清空通過單個角色查到的多個menu_id, 避免重複拼接 SET sMenu_ids_by_roleIds = ''; END WHILE; RETURN sReturn_menu_ids; END; SELECT Menu.menu_id,Menu.sup_menu,Menu.auth_id FROM Menu WHERE FIND_IN_SET(menu_id, getParentLists('15,25,5,26',4)) ORDER BY Menu.menu_id+0 ASC; SELECT Menu.menu_id,Menu.sup_menu,Menu.auth_id FROM Menu WHERE FIND_IN_SET(menu_id, getParentLists('17,5',2)) ORDER BY Menu.menu_id*1 ASC; SELECT Menu.menu_id,Menu.sup_menu,Menu.auth_id FROM Menu WHERE FIND_IN_SET(menu_id, getParentLists('11,5',2)) ORDER BY Menu.menu_id*2 ASC;
到此這篇關於Mysql 實現 向上遞迴查詢父節點並返回樹結構的文章就介紹到這了,更多相關Mysql遞迴查詢父節點內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!
相關文章
<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
综合看Anker超能充系列的性价比很高,并且与不仅和iPhone12/苹果<em>Mac</em>Book很配,而且适合多设备充电需求的日常使用或差旅场景,不管是安卓还是Switch同样也能用得上它,希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
除了L4WUDU与吴亦凡已经多次共事,成为了明面上的厂牌成员,吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节,这也是20XXCLUB首次全员合照,王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
目前应用IPFS的机构:1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆,历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
开拓者的车机是兼容苹果和<em>安卓</em>,虽然我不怎么用,但确实兼顾了我家人的很多需求:副驾的门板还配有解锁开关,有的时候老婆开车,下车的时候偶尔会忘记解锁,我在副驾驶可以自己开门:第二排设计很好,不仅配置了一个很大的
2021-06-01 09:30:48
不仅是<em>安卓</em>手机,苹果手机的降价力度也是前所未有了,iPhone12也“跳水价”了,发布价是6799元,如今已经跌至5308元,降价幅度超过1400元,最新定价确认了。iPhone12是苹果首款5G手机,同时也是全球首款5nm芯片的智能机,它
2021-06-01 09:30:45