数据库的递归查询

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

正文开始：

postgresql:

• -- 向下递归

WITH RECURSIVE r AS (

SELECT * FROM digui_test WHERE id = 3

union ALL

SELECT 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 = 3

union ALL

SELECT 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_id

FROM (

SELECT

@r AS _id,

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

@l := @l + 1 AS lvl

FROM

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

demo h

WHERE @r > 0) T1

JOIN demo T2

ON T1._id = T2.id

ORDER BY T1.lvl DESC;

• 向下递归：

SELECT

t3.id,t3.parent_id

FROM

(

SELECT

t1.id,

t1.parent_id,

IF

(

find_in_set( CONVERT ( parent_id USING utf8 ), @pids ) > 0,

@pids := concat( @pids, ',', id ),

0

) AS ischild

FROM

( SELECT id, parent_id FROM demo t ORDER BY parent_id, id ) t1,

( SELECT @pids := '1' ) t2

) t3

WHERE

ischild != 0

ORDER BY

t3.id;

5.8版本之后：

• 向上递归：

  WITH RECURSIVE r_t AS (
	SELECT
		s1.id,
		s1.parent_id,
		s1.name
	FROM
		demo s1
	WHERE
		s1.id = '1500098801490440193' UNION ALL
	SELECT
		s2.id,
		s2.parent_id,
		s2.name 
	FROM
		demo s2
		INNER JOIN r_t ON r_t.parent_id = s2.id
	) SELECT
	id,parent_id,name
FROM
	r_t

• 向下递归：

WITH RECURSIVE r_t AS (
  SELECT `id`, `parent_id`, `name`
  FROM `demo`
  WHERE `id` = 1 -- where 1 is the parent_id
  UNION ALL
  SELECT `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  查询递归路径。

本文完...