Mysql-树形结构递归查询

<p>新建测试表：</p><blockquote><p>CREATE TABLE USERS (</p><p>  ID VARCHAR(36) NOT NULL COMMENT 'ID',</p><p>  PID VARCHAR(36) DEFAULT NULL COMMENT '父级ID',</p><p>  NAME VARCHAR(36) DEFAULT NULL COMMENT '名称',</p><p>  ENNAME VARCHAR(50) DEFAULT NULL COMMENT '英文名',</p><p>  PWD TINYINT(4) DEFAULT NULL COMMENT '密码',</p><p>  ROLE CHAR(1) DEFAULT NULL COMMENT '角色',</p><p>  PRIMARY KEY (ID)</p><p>) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_0900_AI_CI COMMENT='用户表'; </p><p>
</p></blockquote><p>插入测试数据：</p><blockquote><p>INSERT INTO USERS</p><p>VALUES('A1','0','AA1','BB1',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A2','0','AA2','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A11','A1','AA3','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A21','A2','AA4','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A111','A11','AA5','BB2',1,2);</p><p>INSERT INTO USERS</p><p class="image-package">VALUES('A211','A21','AA6','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A1111','A111','AA7','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A2111','A211','AA8','BB2',1,2);</p><p>INSERT INTO USERS</p><p class="image-package">VALUES('A11111','A1111','AA9','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A21111','A2111','AA10','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A111111','A11111','AA11','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A211111','A21111','AA12','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A1111111','A111111','AA13','BB2',1,2);</p><p>INSERT INTO USERS</p><p>VALUES('A2111111','A211111','AA14','BB2',1,2);</p><p>
</p><p>COMMIT;</p></blockquote><p>查找父节点：</p><blockquote><p>DROP FUNCTION IF EXISTS GETPARENT;</p><p>CREATE FUNCTION GETPARENT(ROOTID VARCHAR(36))</p><p>  RETURNS VARCHAR(1000)</p><p>DETERMINISTIC</p><p>  BEGIN</p><p>    DECLARE PTEMP VARCHAR(1000);</p><p>    DECLARE CTEMP VARCHAR(1000);</p><p>    SET PTEMP = '#';</p><p>    SET CTEMP = ROOTID;</p><p>    WHILE CTEMP IS NOT NULL DO</p><p>      SET PTEMP = CONCAT(PTEMP, ',', CTEMP);</p><p>      SELECT GROUP_CONCAT(ID) INTO CTEMP</p><p>      FROM USERS</p><p>      WHERE ID = ( SELECT PID FROM USERS WHERE FIND_IN_SET(ID,CTEMP) > 0);</p><p>    END WHILE;</p><p>    RETURN PTEMP;</p><p>  END;</p><p>
</p></blockquote><p>查找子节点：</p><blockquote><p>DROP FUNCTION IF EXISTS GETCHILD;</p><p>CREATE FUNCTION  GETCHILD(ROOTID VARCHAR(36))</p><p>  RETURNS VARCHAR(1000)</p><p>DETERMINISTIC</p><p>  BEGIN</p><p>    DECLARE PTEMP VARCHAR(1000);</p><p>    DECLARE CTEMP VARCHAR(1000);</p><p>    SET PTEMP = '#';</p><p>    SET CTEMP = ROOTID;</p><p>    WHILE CTEMP IS NOT NULL DO</p><p>      SET PTEMP = CONCAT(PTEMP, ',', CTEMP);</p><p>      SELECT GROUP_CONCAT(ID) INTO CTEMP</p><p>      FROM USERS</p><p>      WHERE ID = ( SELECT ID FROM USERS WHERE FIND_IN_SET(PID,CTEMP) > 0);</p><p>    END WHILE;</p><p>    RETURN PTEMP;</p><p>  END;</p><p>
</p></blockquote><p>查询语句：</p><blockquote><p>SELECT * FROM USERS WHERE FIND_IN_SET(ID,GETPARENT('A11111'));</p></blockquote><p class="image-package"><img class="uploaded-img" src="https://img.haomeiwen.com/i9645324/57ac08f91933ecdf.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p><blockquote><p>SELECT COUNT(*) FROM USERS WHERE FIND_IN_SET(ID,GETPARENT('A11111'));</p></blockquote><p class="image-package"><img class="uploaded-img" src="https://img.haomeiwen.com/i9645324/75e80dd7cdd7848c.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><blockquote><p>SELECT * FROM USERS WHERE FIND_IN_SET(ID,GETCHILD('A11111'));</p></blockquote><p class="image-package"><img class="uploaded-img" src="https://img.haomeiwen.com/i9645324/bbc82e91b8121f5c.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p><p>第二种方法：</p><p>自连接查询</p><blockquote><p>SELECT T1.ID,T1.NAME,T2.NAME PARENTNODECONTENT FROM USERS T1</p><p>LEFT JOIN USERS T2</p><p>ON T1.PID = T2.ID</p><p>
</p></blockquote><p> </p><p> </p><p class="image-package"><img class="uploaded-img" src="https://img.haomeiwen.com/i9645324/baab619a1fbd56ce.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p><p>
</p><p>
</p><p>
</p><p>
</p><p>
</p>