sql语句实现递归查询所有节点,mysql和oracle都能用的

首先说一下Oracle的递归查询，相信大部分人都知道很简单。无非start with connect by 函数。下面是从pId向子节点递归查询的例子，unId是数据库表中的主键。

如果是从子节点递归到父节点查询，就把start with 换成unid，prior左右对换

下面再讲MySql 的递归查询方式。MySql没有Oracle的强大功能，虽然都是同一个公司的产品。所以只能靠自己写。有很多方法，用sql去循环查询，或者写存储过程，我这里只提供一种。就是新建一个function函数。

表结构不说了，无非就是 Id ，pId，其他列。下面是创建一个递归查询子节点的函数

DROP FUNCTION IF EXISTS queryChildrenPowerInfo

CREATE FUNCTION `queryChildrenPowerInfo` (powerId VARCHAR(2000))

RETURNS VARCHAR(2000)

BEGIN

DECLARE sTemp VARCHAR(2000)

DECLARE sTempChd VARCHAR(2000)

SET sTemp = '$'

SET sTempChd = cast(powerId as CHAR)

WHILE sTempChd is not NULL DO

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

SELECT group_concat(id) INTO sTempChd FROM t_discretionary_power where FIND_IN_SET(pId,sTempChd)>0

END WHILE

return sTemp

END

调用的时候：select  queryChildrenPowerInfo("fa2528924c7e9168014c9bedfe04039c")该语句会返回Id和父Id等于传入参数powerId的一个字符串，中间有逗号隔开如图

下面这句代码的意思是，查询出 t_discretionary_power  表中，t.id 等于上面查询出的结果集的数据。FIND_IN_SET(A,B)是MYSQL的函数。意思是查找在B集合中有A的数据。相当于In

select t.* from t_discretionary_power  t where FIND_IN_SET(t.id,queryChildrenPowerInfo('fa2528924c7e9168014c9bedfe04039c'))

mysql是可以使用递归函数的,如下例子:

mysql树查询，sql递归函数

select cateID from goodscategory where FIND_IN_SET(cateID ,getGoodsChildLst('43242'))

CREATE FUNCTION `getGoodsChildLst`(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(cateID) INTO sTempChd FROM goodscategory where FIND_IN_SET(parentCateID,sTempChd)>0

END WHILE

RETURN sTemp

END

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

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

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

---