西宁摄网站制作互联网推广渠道

本文总结了postgres、mysql、oracle3种数据库的递归查询样例，包含向上递归和向下递归。

正文开始：

postgresql:

• -- 向下递归

WITH RECURSIVE r AS (SELECT * FROM digui_test WHERE id = 3union ALLSELECT digui_test.* FROM digui_test, r WHERE digui_test.pid = r.id)SELECT * FROM r ORDER BY id;

• -- 向下递归

WITH RECURSIVE r AS (SELECT * FROM digui_test WHERE id = 3union ALLSELECT digui_test.* FROM digui_test, r WHERE digui_test.id = r.pid)SELECT * FROM r ORDER BY id;

mysql:

5.7版本之前：

• 向上递归：

SELECT T2.id, T2.name ,T2.parent_idFROM (SELECT@r AS _id,(SELECT @r := parent_id FROM demo WHERE id = _id) AS parent_id,@l := @l + 1 AS lvlFROM(SELECT @r := 2, @l := 0) vars,demo hWHERE @r > 0) T1JOIN demo T2ON T1._id = T2.idORDER BY T1.lvl DESC;

• 向下递归：

SELECTt3.id,t3.parent_idFROM(SELECTt1.id,t1.parent_id,IF(find_in_set( CONVERT ( parent_id USING utf8 ), @pids ) > 0,@pids := concat( @pids, ',', id ),0) AS ischildFROM( SELECT id, parent_id FROM demo t ORDER BY parent_id, id ) t1,( SELECT @pids := '1' ) t2) t3WHEREischild != 0ORDER BYt3.id;

5.8版本之后：

• 向上递归：

  WITH RECURSIVE r_t AS (SELECTs1.id,s1.parent_id,s1.nameFROMdemo s1WHEREs1.id = '1500098801490440193' UNION ALLSELECTs2.id,s2.parent_id,s2.name FROMdemo s2INNER JOIN r_t ON r_t.parent_id = s2.id) SELECTid,parent_id,name
FROMr_t

• 向下递归：

WITH RECURSIVE r_t AS (SELECT `id`, `parent_id`, `name`FROM `demo`WHERE `id` = 1 -- where 1 is the parent_idUNION ALLSELECT `child`.`id`, `child`.`parent_id`, `child`.`name`FROM `demo` `child`JOIN `r_t` `parent` ON `child`.`parent_id` = `parent`.`id`
)
SELECT * FROM r_t;

oracle:

select * from 表 start with 条件入口 connect by prior id =  parent_id（向下）
select * from 表 start with 条件入口 connect by prior parent_id = id（向上）

如果不写prior，表明前序遍历 未指明遍历方向，所以不进行递归，习惯上会在语句后面加上order by排序和group by分组

      connect_by_root(列名)  查询根节点

      connect_by_isleaf   查询是否叶子节点，0是，1不是

      sys_connect_by_path  查询递归路径。

本文完...