Oracle对于树形查询可以使用start with … connect by
|
1 |
select * from menu start with id='130000' connect by id = prior parent_id; |
mysql随没有自带的语法支持，不过可以通过创建函数来实现递归查询。
一、函数查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
-- 创建演示数据 create table `nodelist` ( `id` int (11), `nodecontent` varchar (300), `pid` int (11) ); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('1','a',NULL); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('2','b','1'); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('3','c','1'); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('4','d','2'); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('5','e','3'); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('6','f','3'); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('7','g','5'); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('8','h','7'); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('9','i','8'); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('10','j','8'); -- 创建一个函数 DROP FUNCTION IF EXISTS `getChild`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `getChild`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8 BEGIN DECLARE ptemp VARCHAR(1000); DECLARE ctemp VARCHAR(1000); SET ptemp = '#'; SET ctemp =CAST(rootId AS CHAR); WHILE ctemp IS NOT NULL DO SET ptemp = CONCAT(ptemp,',',ctemp); SELECT GROUP_CONCAT(id) INTO ctemp FROM nodelist WHERE FIND_IN_SET(pid,ctemp)>0; END WHILE; RETURN ptemp; END$$ DELIMITER ; -- OK，查询可以通过将函数当做一个查询条件。 SELECT * FROM nodelist WHERE FIND_IN_SET(id, getChild(3)) |
二、自连接查询
|
1 2 3 |
SELECT t1.id,t1.nodecontent,t2.nodecontent parentnodecontent FROM nodelist t1 LEFT JOIN nodelist t2 ON t1.pid = t2.id |
借鉴 https://www.jianshu.com/p/f99665266bb1
里面用到的内置函数 https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc