一句SQL实现MYSQL的递归查询 - dukou
一句SQL实现MYSQL的递归查询
2015-07-31 10:48 dukou 阅读(44189) 评论(3) 编辑收藏举报众所周知,目前的mysql版本中并不支持直接的递归查询,但是通过递归到迭代转化的思路,还是可以在一句SQL内实现树的递归查询的。这个得益于Mysql允许在SQL语句内使用@变量。以下是示例代码。
创建表格
CREATE TABLE `treenodes` ( `id` int , -- 节点ID `nodename` varchar (60), -- 节点名称 `pid` int -- 节点父ID );
插入测试数据
INSERT INTO `treenodes` (`id`, `nodename`, `pid`) VALUES (\'1\',\'A\',\'0\'),(\'2\',\'B\',\'1\'),(\'3\',\'C\',\'1\'), (\'4\',\'D\',\'2\'),(\'5\',\'E\',\'2\'),(\'6\',\'F\',\'3\'), (\'7\',\'G\',\'6\'),(\'8\',\'H\',\'0\'),(\'9\',\'I\',\'8\'), (\'10\',\'J\',\'8\'),(\'11\',\'K\',\'8\'),(\'12\',\'L\',\'9\'), (\'13\',\'M\',\'9\'),(\'14\',\'N\',\'12\'),(\'15\',\'O\',\'12\'), (\'16\',\'P\',\'15\'),(\'17\',\'Q\',\'15\'),(\'18\',\'R\',\'3\'), (\'19\',\'S\',\'2\'),(\'20\',\'T\',\'6\'),(\'21\',\'U\',\'8\');
查询语句
SELECT id AS ID,pid AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM ( SELECT id,pid, @le:= IF (pid = 0 ,0, IF( LOCATE( CONCAT(\'|\',pid,\':\'),@pathlevel) > 0 , SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT(\'|\',pid,\':\'),-1),\'|\',1) +1 ,@le+1) ) levels , @pathlevel:= CONCAT(@pathlevel,\'|\',id,\':\', @le ,\'|\') pathlevel , @pathnodes:= IF( pid =0,\',0\', CONCAT_WS(\',\', IF( LOCATE( CONCAT(\'|\',pid,\':\'),@pathall) > 0 , SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT(\'|\',pid,\':\'),-1),\'|\',1) ,@pathnodes ) ,pid ) )paths ,@pathall:=CONCAT(@pathall,\'|\',id,\':\', @pathnodes ,\'|\') pathall FROM treenodes, (SELECT @le:=0,@pathlevel:=\'\', @pathall:=\'\',@pathnodes:=\'\') vv ORDER BY pid,id ) src ORDER BY id
最后的结果如下:
ID 父ID 父到子之间级数 父到子路径 ------ ------ ------------ --------------- 1 0 0 ,0 2 1 1 ,0,1 3 1 1 ,0,1 4 2 2 ,0,1,2 5 2 2 ,0,1,2 6 3 2 ,0,1,3 7 6 3 ,0,1,3,6 8 0 0 ,0 9 8 1 ,0,8 10 8 1 ,0,8 11 8 1 ,0,8 12 9 2 ,0,8,9 13 9 2 ,0,8,9 14 12 3 ,0,8,9,12 15 12 3 ,0,8,9,12 16 15 4 ,0,8,9,12,15 17 15 4 ,0,8,9,12,15 18 3 2 ,0,1,3 19 2 2 ,0,1,2 20 6 3 ,0,1,3,6 21 8 1 ,0,8