计划,每天学习两小时,坚持带来大改变。

MYSQL:如何做递归层次查询

MySQL 阿尤 2432浏览 0评论
        最近在做无限级分类的时间遇到一个问题,就是数据表里面存的都是分类id.如果我点开父分类ID则只显示父分类ID,而子分类的数据不显示,个人感觉是不合理的.于是就在网上寻找了关于mysql将父分类ID和其所有子分类ID全部查询出来的方法.   

        当然想要用mysql实现递归查询,根本做不到!!! 可是经过我数天茶不思饭不想的刻苦琢磨,终于想到了一个合理的,适用于mysql和其他sql的解决方案。

        方案一出,就秋风扫落叶之势,席卷整个dao层~~~所到之处,所有问题迎刃而解,让所有问题都不再为问题 都成为了我这个函数的炮灰而已。。。 
         话不多说待我把解决方法仔细道来~~~~~ 
         下面是sql脚本,想要运行一下 把下边的粘贴复制下来,做一个treenodes.sq直接运行便是。。。 
/* 
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'); 
--------------------------------------------------- 
上边是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:G 
8: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 ; 
使用我们直接利用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)) 
); 
子查询的结果集是 
+--------+ 
id 
---- 
+-------+ 
然后经过外层查询就是 
id  pid 
3   1 
6   3 
6   6 
--------- 
好了 Perfect
 

以上内容参考自 互联网,经过本人整理.

转载请注明:阿尤博客 » MYSQL:如何做递归层次查询

游客
发表我的评论 换个身份
取消评论

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  • 验证码 (必填)点击刷新验证码