mysql 有没有类似 orcale connect by d

mysql数据库实现类似oracle connect by prior的查询

--注下面的语句用到了表

DROP TABLE IF EXISTS `a_sys_org`

CREATE TABLE `a_sys_org` (

`org_id` varchar(32) NOT NULL COMMENT '机构号',

`org_name` varchar(256) NOT NULL COMMENT '机构名称',

`parent_org_id` varchar(32) NOT NULL COMMENT '父机构号',

PRIMARY KEY (`org_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统机构表'

-- 方法1，函数实现

-- 注意 SELECT getChildOrg('00010')

-- 最大支持字符串连接102400

DROP FUNCTION IF EXISTS getChildidList

CREATE FUNCTION getChildidList(rootId VARCHAR(32))

RETURNS text

BEGIN

DECLARE sTemp text

DECLARE sTempChd text

SET@@group_concat_max_len = 102400

SET sTemp = '$'

SET sTempChd = rootId

WHILE sTempChd IS NOT NULL DO

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

SELECT

group_concat(org_id) INTO sTempChd

FROM

a_sys_org

WHERE

FIND_IN_SET(parent_org_id, sTempChd) >0

END WHILE

SET@@group_concat_max_len = 1024

RETURN SUBSTRING(sTemp,3)

END

-- 方法2，存储过程+中间表

-- 存储过程实现mysql递归查询，类似oracle start with connect by prior

-- 注意 call getChildOrg('00010')

-- 递归有深度限制，最大是255层

DROP PROCEDURE IF EXISTS getChildOrg

CREATE PROCEDURE getChildOrg (IN rootid VARCHAR(32))

BEGIN

DECLARE _level_var INT

DROP TABLE IF EXISTS temp_child_list

CREATE TABLE temp_child_list (

_id VARCHAR(32),

_level INT

)

SET _level_var = 0

INSERT INTO temp_child_list (_id, _level) VALUE(rootid,_level_var)

SET _level_var = _level_var + 1

INSERT INTO temp_child_list (_id, _level)

SELECT

org_id,

_level_var

FROM

a_sys_org

WHERE

parent_org_id = rootid

WHILE ROW_COUNT() >0

DO

SET _level_var = _level_var + 1

INSERT INTO temp_child_list SELECT

a.org_id,

_level_var

FROM

a_sys_org a,

temp_child_list b

WHERE

a.parent_org_id = b._id

AND b._level = _level_var - 1

END WHILE

SELECT _id FROM temp_child_list ORDER BY _level

DROP TABLE IF EXISTS temp_child_list

END

-- 方法3，存储过程+临时表

-- 存储过程实现mysql递归查询，类似oracle start with connect by prior

-- 注意 call getChildOrg('00010')

-- 递归有深度限制，最大是255层

-- 此存储过程利用了临时表

DROP PROCEDURE IF EXISTS showChildList

CREATE PROCEDURE showChildList (IN rootId VARCHAR(32))

BEGIN

SET@@max_sp_recursion_depth=99

DROP TEMPORARY TABLE IF EXISTS temp_child_list

CREATE TEMPORARY TABLE temp_child_list ( sno INT PRIMARY KEY auto_increment, _id VARCHAR (32), _depth INT )

-- 插入当前节点

INSERT INTO temp_child_list (_id, _depth) VALUES (rootId ,- 1)

-- 插入子节点

CALL createChildList (rootId, 0)

SELECT o.org_id FROM temp_child_list t, a_sys_org o WHERE t._id = o.org_id ORDER BY t.sno

END

DROP PROCEDURE IF EXISTS createChildList

CREATE PROCEDURE createChildList ( IN rootId VARCHAR (32), IN depth_var INT )

BEGIN

DECLARE done INT DEFAULT 0

DECLARE b VARCHAR (32)

DECLARE cur1 CURSOR FOR SELECT org_id FROM a_sys_org WHERE parent_org_id = rootId

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

INSERT INTO temp_child_list (_id, _depth) SELECT org_id, depth_var FROM a_sys_org WHERE parent_org_id = rootId

OPEN cur1

FETCH cur1 INTO b

WHILE done = 0 DO CALL createChildList (b, depth_var + 1)

FETCH cur1 INTO b

END WHILE

CLOSE cur1

END

单表自身关联查询，关联条件就是父节点pcode和code相等，查询字段包含pcode和sorce，将查询结果作为新表按pcode分组，用group by，查询字段是count记录数，这样就获取pcode的节点值，这是整体思路

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

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

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

---