精华内容
下载资源
问答
  • mysql递归

    2021-01-18 19:46:11
    sql Server可以用with as 语法,mysql没有这个功能,只能用别的方式了,目前的mysql版本中并不支持直接的递归查询,但是通过递归到迭代转化的思路,还是可以在一句SQL内实现树的递归查询的。这个得益于Mysql允许在...

    sql Server可以用with as 语法,mysql没有这个功能,只能用别的方式了,目前的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 复制代码

    展开全文
  • 本文目的为对比mysql递归树两种查询方式效率。工具/原料--创建表DROP TABLE IF EXISTS `t_areainfo`;CREATE TABLE `t_areainfo` (`id` int(11) NOT '0' AUTO_INCREMENT,`level` int(11) DEFAULT '0',`name` varchar...

    本文目的为对比mysql递归树两种查询方式效率。

    aeffb6cab189872a931778e3ee13fc8d.png

    edaddf009d5eeabebaa6a1ccbb5e450d.png

    工具/原料

    --创建表

    DROP TABLE IF EXISTS `t_areainfo`;

    CREATE TABLE `t_areainfo` (

    `id` int(11) NOT '0' AUTO_INCREMENT,

    `level` int(11) DEFAULT '0',

    `name` varchar(255) DEFAULT '0',

    `parentId` int(11) DEFAULT '0',

    `status` int(11) DEFAULT '0',

    PRIMARY KEY (`id`)

    ) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

    2

    --初始数据

    INSERT INTO `t_areainfo` VALUES ('1', '0', '中国', '0', '0');

    INSERT INTO `t_areainfo` VALUES ('2', '0', '华北区', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('3', '0', '华南区', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('4', '0', '北京', '2', '0');

    INSERT INTO `t_areainfo` VALUES ('5', '0', '海淀区', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('6', '0', '丰台区', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('7', '0', '朝阳区', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('8', '0', '北京XX区1', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('9', '0', '北京XX区2', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('10', '0', '北京XX区3', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('11', '0', '北京XX区4', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('12', '0', '北京XX区5', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('13', '0', '北京XX区6', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('14', '0', '北京XX区7', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('15', '0', '北京XX区8', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('16', '0', '北京XX区9', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('17', '0', '北京XX区10', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('18', '0', '北京XX区11', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('19', '0', '北京XX区12', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('20', '0', '北京XX区13', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('21', '0', '北京XX区14', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('22', '0', '北京XX区15', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('23', '0', '北京XX区16', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('24', '0', '北京XX区17', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('25', '0', '北京XX区18', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('26', '0', '北京XX区19', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('27', '0', '北京XX区1', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('28', '0', '北京XX区2', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('29', '0', '北京XX区3', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('30', '0', '北京XX区4', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('31', '0', '北京XX区5', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('32', '0', '北京XX区6', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('33', '0', '北京XX区7', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('34', '0', '北京XX区8', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('35', '0', '北京XX区9', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('36', '0', '北京XX区10', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('37', '0', '北京XX区11', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('38', '0', '北京XX区12', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('39', '0', '北京XX区13', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('40', '0', '北京XX区14', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('41', '0', '北京XX区15', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('42', '0', '北京XX区16', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('43', '0', '北京XX区17', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('44', '0', '北京XX区18', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('45', '0', '北京XX区19', '4', '0');

    INSERT INTO `t_areainfo` VALUES ('46', '0', 'xx省1', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('47', '0', 'xx省2', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('48', '0', 'xx省3', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('49', '0', 'xx省4', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('50', '0', 'xx省5', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('51', '0', 'xx省6', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('52', '0', 'xx省7', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('53', '0', 'xx省8', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('54', '0', 'xx省9', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('55', '0', 'xx省10', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('56', '0', 'xx省11', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('57', '0', 'xx省12', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('58', '0', 'xx省13', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('59', '0', 'xx省14', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('60', '0', 'xx省15', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('61', '0', 'xx省16', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('62', '0', 'xx省17', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('63', '0', 'xx省18', '1', '0');

    INSERT INTO `t_areainfo` VALUES ('64', '0', 'xx省19', '1', '0');

    方式一:采用function获取所有子节点的id

    --查询传入areaId及其以下所有子节点

    DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

    CREATE FUNCTION `queryChildrenAreaInfo` (areaId INT)

    RETURNS VARCHAR(4000)

    BEGIN

    DECLARE sTemp VARCHAR(4000);

    DECLARE sTempChd VARCHAR(4000);

    SET sTemp = '$';

    SET sTempChd = cast(areaId as char);

    WHILE sTempChd is not NULL DO

    SET sTemp = CONCAT(sTemp,',',sTempChd);

    SELECT group_concat(id) INTO sTempChd FROM t_areainfo where FIND_IN_SET(parentId,sTempChd)>0;

    END WHILE;

    return sTemp;

    END;

    --调用方式

    select queryChildrenAreaInfo(1);

    select * from t_areainfo where FIND_IN_SET(id, queryChildrenAreaInfo(1));

    方式二:采用临时表和存储过程完成

    -- 创建存储过程

    drop PROCEDURE showChildList;

    CREATE PROCEDURE showChildList (IN rootId INT)

    BEGIN

    CREATE TEMPORARY TABLE

    IF NOT EXISTS tmpList (

    sno INT PRIMARY KEY auto_increment,

    id INT,

    depth INT

    );

    DELETE FROM tmpList;

    CALL createChildList (rootId, 0);

    SELECT tmpList.*, t_areainfo.* FROM tmpList, t_areainfo

    WHERE

    tmpList.id = t_areainfo.id

    ORDER BY

    tmpList.sno;

    END;

    drop PROCEDURE createChildList;

    CREATE PROCEDURE createChildList (IN rootId INT, IN nDepth INT)

    BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE b INT;

    DECLARE cur1 CURSOR FOR SELECT id FROM t_areainfo WHERE parentId = rootId;

    DECLARE CONTINUE HANDLER FOR NOT FOUND

    SET done = 1;

    INSERT INTO tmpList VALUES (NULL, rootId, nDepth);

    OPEN cur1;

    FETCH cur1 INTO b;

    WHILE done = 0 DO

    CALL createChildList (b, nDepth + 1);

    FETCH cur1 INTO b;

    END WHILE;

    CLOSE cur1;

    END;

    -- 调用方式

    call showChildList(1);

    两种方式对比:

    --简易程度

    首先我们可以通过sql语句就可以看的出,方式二的代码量差不多是方式一的两倍,而且又是临时表又是游标的,极易出错。

    --效率对比

    可以通过图片可以看到,同样的查询结果,方式一仅仅需要0.044s既可以完成查询,而方式二则需要1.525s,效率远远低于方式一。

    注意事项

    执行方式二是系统出报错,错误原因是因为没有指定控制递归调用层数上线,可以通过利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。

    展开全文
  • 原标题:SQL如何实现MYSQL的递归查询,SQL实现MYSQL递归猿哥解读本文的亮点在于很多PHPer肯定不知道MySQL还能这么用。所周知,目前的mysql版本中并不支持直接的递归查询,但是通过递归到迭代转化的思路,还是可以在...

    原标题:SQL如何实现MYSQL的递归查询,SQL实现MYSQL递归

    猿哥解读

    本文的亮点在于很多PHPer肯定不知道MySQL还能这么用。

    所周知,目前的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

    以上就是一句SQL实现MYSQL的递归查询的实现全过程,希望对大家的学习有所帮助。

    关注PHP技术大全,成长不止一点点!

    分享出去,让知识地带无限蔓延!返回搜狐,查看更多

    责任编辑:

    展开全文
  • MySQL的目前版本中还没有对应的功能。在MySQL中如果是有限的层次,比如事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4,则可以直接通过left join 来实现。但有时无法控制树的深度。...

    在Oracle 中有一个 Hierarchical Queries 通过CONNECT BY 可以方便的查了所有当前节点下的所有子节点。

    在MySQL的目前版本中还没有对应的功能。

    在MySQL中如果是有限的层次,比如事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4,则可以直接通过left join 来实现。

    但有时无法控制树的深度。这时就需要在MySQL中用存储过程来实现或在你的程序中来实现这个递归。

    本文讨论一下几种实现的方法。

    样例数据:

    复制代码 代码示例:

    mysql> create table treeNodes

    -> (

    ->  id int primary key,

    ->  nodename varchar(20),

    ->  pid int

    -> );

    Query OK, 0 rows affected (0.09 sec)

    mysql> select * from treenodes;

    +----+----------+------+

    | id | nodename | pid  |

    +----+----------+------+

    |  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 |

    +----+----------+------+

    17 rows in set (0.00 sec)

    树形图:

    1:A

    +-- 2:B

    |    +-- 4:D

    |    +-- 5:E

    +-- 3:C

    +-- 6:F

    +-- 7:G

    8:H

    +-- 9:I

    |    +-- 12:L

    |    |    +--14:N

    |    |    +--15:O

    |    |        +--16:P

    |    |        +--17:Q

    |    +-- 13:M

    +-- 10:J

    +-- 11:K

    方法一:利用函数来得到所有子节点号。

    创建一个function getChildLst, 得到一个由所有子节点号组成的字符串.

    复制代码 代码示例:

    mysql> delimiter //

    mysql>

    mysql> CREATE FUNCTION `getChildLst`(rootId INT)

    -> RETURNS varchar(1000)

    -> BEGIN

    ->   DECLARE sTemp VARCHAR(1000);

    ->   DECLARE sTempChd VARCHAR(1000);

    ->

    ->   SET sTemp = '$';

    ->   SET sTempChd =cast(rootId as CHAR);

    ->

    ->   WHILE sTempChd is not null DO

    ->     SET sTemp = concat(sTemp,',',sTempChd);

    ->     SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;

    ->   END WHILE;

    ->   RETURN sTemp;

    -> END

    -> //

    Query OK, 0 rows affected (0.00 sec)

    mysql>

    mysql> delimiter ;

    使用直接利用find_in_set函数配合这个getChildlst来查找:

    复制代码 代码示例:

    mysql> select getChildLst(1);

    +-----------------+

    | getChildLst(1)  |

    +-----------------+

    | $,1,2,3,4,5,6,7 |

    +-----------------+

    1 row in set (0.00 sec)

    mysql> select * from treeNodes

    -> where FIND_IN_SET(id, getChildLst(1));

    +----+----------+------+

    | id | nodename | pid  |

    +----+----------+------+

    |  1 | A        |    0 |

    |  2 | B        |    1 |

    |  3 | C        |    1 |

    |  4 | D        |    2 |

    |  5 | E        |    2 |

    |  6 | F        |    3 |

    |  7 | G        |    6 |

    +----+----------+------+

    7 rows in set (0.01 sec)

    mysql> select * from treeNodes

    -> where FIND_IN_SET(id, getChildLst(3));

    +----+----------+------+

    | id | nodename | pid  |

    +----+----------+------+

    |  3 | C        |    1 |

    |  6 | F        |    3 |

    |  7 | G        |    6 |

    +----+----------+------+

    3 rows in set (0.01 sec)

    优点: 简单,方便,没有递归调用层次深度的限制 (max_sp_recursion_depth,最大255) ;

    缺点:长度受限,虽然可以扩大 RETURNS varchar(1000),但总是有最大限制的。

    MySQL目前版本( 5.1.33-community)中还不支持function 的递归调用。

    方法二:利用临时表和过程递归

    创建存储过程如下。createChildLst 为递归过程,showChildLst为调用入口过程,准备临时表及初始化。

    复制代码 代码示例:

    mysql> delimiter //

    mysql>

    mysql> # 入口过程

    mysql> CREATE PROCEDURE showChildLst (IN rootId INT)

    -> BEGIN

    ->  CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst

    ->   (sno int primary key auto_increment,id int,depth int);

    ->  DELETE FROM tmpLst;

    ->

    ->  CALL createChildLst(rootId,0);

    ->

    ->  select tmpLst.*,treeNodes.* from tmpLst,treeNodes where tmpLst.id=treeNodes.id order by tmpLst.sno;

    -> END;

    -> //

    Query OK, 0 rows affected (0.00 sec)

    mysql>

    mysql> # 递归过程

    mysql> CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)

    -> BEGIN

    ->  DECLARE done INT DEFAULT 0;

    ->  DECLARE b INT;

    ->  DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;

    ->  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    ->

    ->  insert into tmpLst values (null,rootId,nDepth);

    ->

    ->  OPEN cur1;

    ->

    ->  FETCH cur1 INTO b;

    ->  WHILE done=0 DO

    ->          CALL createChildLst(b,nDepth+1);

    ->          FETCH cur1 INTO b;

    ->  END WHILE;

    ->

    ->  CLOSE cur1;

    -> END;

    -> //

    Query OK, 0 rows affected (0.00 sec)

    mysql> delimiter ;

    调用时传入结点:

    复制代码 代码示例:

    mysql> call showChildLst(1);

    +-----+------+-------+----+----------+------+

    | sno | id   | depth | id | nodename | pid  |

    +-----+------+-------+----+----------+------+

    |   4 |    1 |     0 |  1 | A        |    0 |

    |   5 |    2 |     1 |  2 | B        |    1 |

    |   6 |    4 |     2 |  4 | D        |    2 |

    |   7 |    5 |     2 |  5 | E        |    2 |

    |   8 |    3 |     1 |  3 | C        |    1 |

    |   9 |    6 |     2 |  6 | F        |    3 |

    |  10 |    7 |     3 |  7 | G        |    6 |

    +-----+------+-------+----+----------+------+

    7 rows in set (0.13 sec)

    Query OK, 0 rows affected, 1 warning (0.14 sec)

    mysql>

    mysql> call showChildLst(3);

    +-----+------+-------+----+----------+------+

    | sno | id   | depth | id | nodename | pid  |

    +-----+------+-------+----+----------+------+

    |   1 |    3 |     0 |  3 | C        |    1 |

    |   2 |    6 |     1 |  6 | F        |    3 |

    |   3 |    7 |     2 |  7 | G        |    6 |

    +-----+------+-------+----+----------+------+

    3 rows in set (0.11 sec)

    Query OK, 0 rows affected, 1 warning (0.11 sec)

    depth 为深度,这样可以在程序进行一些显示上的格式化处理。类似于oracle中的 level 伪列。sno 仅供排序控制。

    这样还可以通过临时表tmpLst与数据库中其它表进行联接查询。

    MySQL中可以利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。如下例设为12.

    复制代码 代码示例:

    mysql> set max_sp_recursion_depth=12;

    Query OK, 0 rows affected (0.00 sec)

    优点 : 可以更灵活处理,及层数的显示。并且可以按照树的遍历顺序得到结果。

    缺点 : 递归有255的限制。

    方法三:利用中间表和过程

    (本方法由yongyupost2000提供样子改编)

    创建存储过程如下。

    由于MySQL中不允许在同一语句中对临时表多次引用,只以使用普通表tmpLst来实现了。

    当然程序中负责在用完后清除这个表。

    复制代码 代码示例:

    delimiter //

    drop PROCEDURE IF EXISTS  showTreeNodes_yongyupost2000//

    CREATE PROCEDURE showTreeNodes_yongyupost2000 (IN rootid INT)

    BEGIN

    DECLARE Level int ;

    drop TABLE IF EXISTS tmpLst;

    CREATE TABLE tmpLst (

    id int,

    nLevel int,

    sCort varchar(8000)

    );

    Set Level=0 ;

    INSERT into tmpLst SELECT id,Level,ID FROM treeNodes WHERE PID=rootid;

    WHILE ROW_COUNT()>0 DO

    SET Level=Level+1 ;

    INSERT into tmpLst

    SELECT A.ID,Level,concat(B.sCort,A.ID) FROM treeNodes A,tmpLst B

    WHERE  A.PID=B.ID AND B.nLevel=Level-1  ;

    END WHILE;

    END;

    //

    delimiter ;

    CALL showTreeNodes_yongyupost2000(0);

    执行完后会产生一个tmpLst表,nLevel 为节点深度,sCort 为排序字段。

    使用方法:

    复制代码 代码示例:

    SELECT concat(SPACE(B.nLevel*2),'+--',A.nodename)

    FROM treeNodes A,tmpLst B

    WHERE A.ID=B.ID

    ORDER BY B.sCort;

    +--------------------------------------------+

    | concat(SPACE(B.nLevel*2),'+--',A.nodename) |

    +--------------------------------------------+

    | +--A                                       |

    |   +--B                                     |

    |     +--D                                   |

    |     +--E                                   |

    |   +--C                                     |

    |     +--F                                   |

    |       +--G                                 |

    | +--H                                       |

    |   +--J                                     |

    |   +--K                                     |

    |   +--I                                     |

    |     +--L                                   |

    |       +--N                                 |

    |       +--O                                 |

    |         +--P                               |

    |         +--Q                               |

    |     +--M                                   |

    +--------------------------------------------+

    17 rows in set (0.00 sec)

    以上介绍了mysql递归查询的实现方法与实例代码,希望对大家有所帮助。

    展开全文
  • mysql递归查询

    2021-01-27 00:31:08
    +----+----------+------+ | id | nodename | pid | +----+----------+------+ | 3 | C | 1 | | 6 | F | 3 | | 7 | G | 6 | +----+----------+------+ 优点:简单,方便,没有递归调用层次深度的限制 (max_sp_...
  • mysql 递归查询

    2021-01-28 05:59:41
    包含mysql 递归查询父节点 和子节点 包含mysql 递归查询父节点 和子节点 mysql递归查询,查父集合,查子集合 查子集合 --drop FUNCTION `getChildList` CREATE FUNCTION `getChi ... MySQL递归查询_函数语法检查_...
  • 现在大家应该都知道mysql版本中已经不支持直接的递归查询了,但是我们可以通过递归到迭代转化的思路是实现树的递归查询,接下来我们就一起去看看SQL实现MYSQL递归查询的方法。创建表格CREATE TABLE `treenodes` (`id...
  • Mysql递归连接

    2021-01-28 01:40:04
    基于这种结构create table category (id int, mr varchar(10), category_fk int);create table products (id int, name varchar(100), category_fk int);insert category values(1, 'cat1', null),(2, 'cat2', 1),(3...
  • Mysql 递归函数

    2021-02-10 02:48:33
    Mysql 没有递归的参数,需要自定义函数,才能使用递归,方法如下,仅供参考:1.在数据库定义如下方法;DETERMINISTIC (注,有主从的情况下使用,数据一致性)BEGINDECLAREsTemp VARCHAR(4000);DECLAREsTempChd VARCHAR...
  • 例子,mysql递归查询代码。复制代码 代码示例:DROP TABLE IF EXISTS `treenodes`;CREATE TABLE `treenodes` (`id` int(11) NOT NULL,`nodename` varchar(20) DEFAULT NULL,`pid` int(11) DEFAULT NULL,PRIMARY KEY ...
  • Mysql 递归查询

    2021-01-28 05:59:40
    向下递归 DROP FUNCTION IF EXISTS queryChildrenAreaInfo; DELIMITER ;;CREATE FUNCTION queryChildrenAreaInfo(areaId INT)RETURNS VARCHAR(4000)BEGINDECLARE sTemp VARCHAR(4000);DECLARE sTempChd...
  • MySql递归查询,mysql中从子类ID查询所有父类(做无限分类经常用到) 由于mysql 不支持类似 Oracle with ...connect的 递归查询语法 之前一直以为类似的查询要么用存储过程要么只能用程序写递归查询. 现在发现...
  • mysql 递归查找父类

    2021-02-11 00:31:11
    drop function getParentList ;create function getParentList(subId varchar(100),iv_year int)Returns varchar(1000)begindeclare fid varchar(100) default '';declare temp varchar(1000) default '';...
  • Unfortunately MYSQL doesn't support recursive queries, so you'll need to be a little creative here. Something like this could work: select flag, sum(calc) from ( select flag, (num-if(@prevflag=flag,@...
  • MYSQL递归查询所有父节点 1、表结构: CREATETABLE`t_busi_system`( `ID`varchar(64)NOTNULLCOMMENT'标识', `PARENT_ID`varchar(64)DEFAULTNULLCOMMENT'父id', `CREATE_DATE`varchar(64)DEFAULTNULLCOMMENT'创建...
  • 在本教程中,您将了解MySQL递归CTE(公共表表达式)以及如何使用它来遍历分层数据。自MySQL 8.0版以来简要介绍了公共表表达式或叫CTE的功能,因此需要您在计算机上安装MySQL 8.0,以便在本教程中练习本语句。1. MySQL...
  • mysql 递归函数写法

    2021-01-27 21:23:45
    递归函数写法CREATE FUNCTION dgsf (areaId VARCHAR(1000))RETURNS VARCHAR(4000)BEGINDECLARE sTemp VARCHAR(4000);DECLARE sTempChd VARCHAR(4000);SET sTemp = '$';SET sTempChd = areaId;WHILE sTempChd is not ...
  • 查阅了很多mysql迷们自己实现的递归查询:1.use group_concat find_in_set 循环查询结果集,把结果集返回。用存储过程实现。这个有弊端的,而且有个bug。不解原因。限制是procedure对varchar的...
  • mysql 递归排序查询 备注:producttype 排序表,producttype。ptype父节点 ,producttype 。id 主键,showTreeNodes (IN rootimysql 递归排序查询备注:producttype 排序表,producttype。ptype父节点 ,producttype...
  • ​在oracle中我们可以使用connect by prior 函数来实现组织树表递归的查询,但是在mysql中却没有该方法,为了能够使用该方法我们就得自己创建函数来实现该组织树的递归查询。以下实现了2种方式来满足子节点的递归...
  • MYSQL 递归父子

    2021-04-28 06:06:32
    捕获.PNG子查父 (根据子节点查询所有父节点)SELECTT2.*FROM(SELECT@r AS _id,(SELECT@r := parent_idFROMt_sys_organizationWHEREid = _id) AS parent_id,@l := @l + 1 AS lvlFROM(SELECT @r := '4', @l := 0) vars,...
  • " >select * from (select a.*, *, b.name as as department_namefrom t_employee a left join t_department b on on a.department_idt_id = = b.id) xis_deleted = 0and id = #{id,jdbcType=INTEGER}and code .....

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 56,631
精华内容 22,652
关键字:

mysql递归

mysql 订阅
友情链接: EEMD.rar