网站建设鸿儒,seo快速排名软件首页,seo是指什么职位,建站平台费用本文总结了postgres、mysql、oracle3种数据库的递归查询样例#xff0c;包含向上递归和向下递归。
正文开始#xff1a;
postgresql: -- 向下递归
WITH RECURSIVE r AS (SELECT * FROM digui_test WHERE id 3union ALLSELECT digui_test.* FROM digui_test, r WHERE dig…本文总结了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 (SELECTr 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, nameFROM demoWHERE id 1 -- where 1 is the parent_idUNION ALLSELECT child.id, child.parent_id, child.nameFROM demo childJOIN 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 查询递归路径。
本文完...
