-
SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `treenodes`-- ----------------------------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 (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ------------------------------ Records of treenodes-- ----------------------------INSERT INTO `treenodes` VALUES ('1', 'A', '0');INSERT INTO `treenodes` VALUES ('2', 'B', '1');INSERT INTO `treenodes` VALUES ('3', 'C', '1');INSERT INTO `treenodes` VALUES ('4', 'D', '2');INSERT INTO `treenodes` VALUES ('5', 'E', '2');INSERT INTO `treenodes` VALUES ('6', 'F', '3');INSERT INTO `treenodes` VALUES ('7', 'G', '6');INSERT INTO `treenodes` VALUES ('8', 'H', '0');INSERT INTO `treenodes` VALUES ('9', 'I', '8');INSERT INTO `treenodes` VALUES ('10', 'J', '8');INSERT INTO `treenodes` VALUES ('11', 'K', '8');INSERT INTO `treenodes` VALUES ('12', 'L', '9');INSERT INTO `treenodes` VALUES ('13', 'M', '9');INSERT INTO `treenodes` VALUES ('14', 'N', '12');INSERT INTO `treenodes` VALUES ('15', 'O', '12');INSERT INTO `treenodes` VALUES ('16', 'P', '15');INSERT INTO `treenodes` VALUES ('17', 'Q', '15');www.2cto.com---------------------------------------------------上边是sql脚本,在执行select * 之后显示的结果集如下所示: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:G8:H+-- 9:I| +-- 12:L| | +--14:N| | +--15:O| | +--16:P| | +--17:Q| +-- 13:M+-- 10:J+-- 11:K--------------------------------------------如果给你一个这样的table,让你查询根节点为1下的所有节点记录(注意也包括根节点),,肿麽办?????可能有不少人想到connect by 函数,但是我灰常遗憾的告诉你,咱这儿是mysql!!!好,客观您勒上眼,,我的解决办法是利用函数来得到所有子节点号。闲话少续,看我的解决方法创建一个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 ;www.2cto.com使用我们直接利用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)--------------------------------------------只要按我的做,百发百中弹无虚发,遇到问题万变不离其宗直接粘贴复制就是。。。补充:还可以做嵌套查询:select id,pid from treeNodes where id in(select id from treeNodes where FIND_IN_SET(id, getChildLst(3)));子查询的结果集是www.2cto.com+--------+id----367+-------+然后经过外层查询就是id pid3 16 36 6---------好了 Perfect
mysql如何做递归层次查询
最新推荐文章于 2023-09-11 10:10:20 发布