《Mysql學(xué)習(xí)mysql遞歸查詢實(shí)例解析》要點(diǎn)：
本文介紹了Mysql學(xué)習(xí)mysql遞歸查詢實(shí)例解析，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。
導(dǎo)讀：辦法1,mysql遞歸查詢.
/* Navicat MySQL Data Transfer Source Server : mysql_demo3 Source Server Version : 50521 Sourc...
MYSQL實(shí)例辦法1,mysql遞歸查詢.
?
/*
Navicat MySQL Data Transfer
?
Source Server???????? : mysql_demo3
Source Server Version : 50521
Source Host?????????? : localhost:3306
Source Database?????? : test
?
Target Server Type??? : MYSQL
Target Server Version : 50521
File Encoding???????? : 65001
??
Date: 2012-09-02 21:16:03
*/
?
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');
?
MYSQL實(shí)例上邊是sql腳本,在執(zhí)行select * 之后顯示的結(jié)果集如下：
?
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)
?
MYSQL實(shí)例樹(shù)形圖：
?
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??
?
MYSQL實(shí)例如果給你一個(gè)這樣的table,讓你查詢根節(jié)點(diǎn)為1下的所有節(jié)點(diǎn)記錄(注意也包括根節(jié)點(diǎn))?
可能有不少人想到connect by 函數(shù),但是我灰常遺憾的告訴你,咱這兒是mysql!
?
解決辦法：利用函數(shù)來(lái)得到所有子節(jié)點(diǎn)號(hào).
?
閑話少續(xù),看我的解決辦法
創(chuàng)建一個(gè)function getChildLst, 得到一個(gè)由所有子節(jié)點(diǎn)號(hào)組成的字符串.?
?
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 ;
?
MYSQL實(shí)例?
使用我們直接利用find_in_set函數(shù)配合這個(gè)getChildlst來(lái)查找：
?
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)
?
MYSQL實(shí)例只要按我的做,百發(fā)百中百步穿楊,遇到問(wèn)題萬(wàn)變不離其宗直接粘貼復(fù)制就是.
?
補(bǔ)充：
還可以做嵌套查詢：
?
select id,pid from treeNodes where id in(
???? select id from treeNodes where FIND_IN_SET(id, getChildLst(3))
);
?
MYSQL實(shí)例子查詢的結(jié)果集是：?
+--------+
id
----
3
6
7
+-------+
然后,經(jīng)過(guò)外層查詢就是：
id? pid
3?? 1
6?? 3
6?? 6
---------
mysql遞歸查詢替代函數(shù)實(shí)例
mysql遞歸查詢樹(shù)形葉子
Oracle遞歸查詢樹(shù)形結(jié)構(gòu)
MySQL 遞歸查詢當(dāng)前節(jié)點(diǎn)子節(jié)點(diǎn)
mysql遞歸查詢實(shí)現(xiàn)辦法
Oracle遞歸查詢SQL語(yǔ)句分享
sql2005遞歸查詢的例子
sql遞歸查詢(with cte實(shí)現(xiàn))
sql 遞歸查詢的代碼(圖文)
維易PHP培訓(xùn)學(xué)院每天發(fā)布《Mysql學(xué)習(xí)mysql遞歸查詢實(shí)例解析》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/11585.html
