PostgreSQL 中的递归查询 与oracle 的比较
PostgreSQL 中的递归查询，2种方法：1、用with decursive
WITH RECURSIVE d AS (SELECT d1.id,d1.parent_id,d1.caption FROM course_types d1 where d1.dr = 0 and d1.id='typeId' union ALL SELECT d2.id,d2.parent_id,d2.caption FROM course_types d2, d WHERE d2.dr = 0 and d2.parent_id = d.id) SELECT * FROM d
其中typeId 是初始id
2、用递归函数
CREATE OR REPLACE FUNCTION query_child_dept_auth(character varying, lv1 integer)
RETURNS SETOF w_help_dept AS
$BODY$
DECLARE
itemid ALIAS FOR $1;
itemrecord record;
BEGIN
SELECT s.*,lv1 as lv INTO itemrecord FROM depts swhere s.dr = 0 and s.state = 1 and id=itemid order by s.sort,convert_to(s.caption,'gbk');
RETURN NEXT itemrecord;
IF (select count(1) from depts s where s.dr = 0 and s.state = 1 and s.parent_id=itemrecord.id) >0THEN
for itemrecord in SELECT s.* FROM depts swhere s.dr = 0 and s.state = 1 and s.parent_id=itemrecord.id order by s.sort,convert_to(s.caption,'gbk') LOOP
for itemrecord in select * from query_child_dept_auth (itemrecord.id,(lv1+1)) LOOP
RETURN NEXT itemrecord;
end LOOP;
end LOOP;
END IF;
RETURN;
END;$BODY$
LANGUAGE plpgsql VOLATILE
其中w_help_dept是辅助表，表字段和depts一样，且多个lv字段，lv字段用于模拟oracle递归查询中的level树层数
oracle 递归查询
网上资料蛮多，随便找了个http://cpdw.iyunv.com/blog/625574
作用：对于查询递归树是个好方法
页:
[1]