mysql递归查询语句

mysql递归查询，mysql中从子类ID查询所有父类（做无限分类经常用到）

由于mysql 不支持类似 oracle with ...connect的 递归查询语法

之前一直以为类似的查询要么用存储过程要么只能用程序写递归查询.

现在发现原来一条sql语句也是可以搞定的

先来看数据表的结构如下：

id nameparent_id

---------------------------

1 Home0

2 About 1

3 Contact 1

4 Legal 2

5 Privacy 4

6 Products1

7 Support 1

我要的要求是根据一个分类ID（这个分类ID可能是一个子分类），得到所有的父分类，下面是相应的SQL：

SELECT T2.id, T2.name

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := 5, @l := 0) vars,

table1 h

WHERE @r <>0) T1

JOIN table1 T2

ON T1._id = T2.id

ORDER BY T1.lvl DESC

代码@r := 5标示查询id为5的所有父类。结果如下

1, ‘Home’

2, ‘About’

4, ‘Legal’

5, ‘Privacy’

自己仿照这看一下！

给你个网上写的比较好的例子：

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

创建一个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)

---