Created
October 31, 2014 07:43
-
-
Save s4553711/9a5e789bfa69e214acd2 to your computer and use it in GitHub Desktop.
以下昰一個Recursive query的範例,只使用到一個表node,其中以id及parent組成其樹狀目錄結構的資訊。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE node | |
( | |
id integer NOT NULL DEFAULT nextval('nodefolder_id_seq'::regclass), | |
name character varying(20) NOT NULL, | |
parent integer, | |
type integer NOT NULL | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH RECURSIVE node_rec(id, name, parent, type) as ( | |
-- Non-recursive term | |
( | |
select id, name, parent, type from node where id = 566 | |
) | |
UNION ALL | |
-- Recursive Term | |
-- Here I use 'UNION ALL' statement so that the duplicate result will be included. | |
-- You can choose to use 'UNION' instead of 'UNION ALL' to get distinct data. | |
( | |
select c.id, c.name , c.parent, c.type from node_rec AS p, node AS c | |
where c.parent = p.id | |
) | |
) | |
select id, name, parent, type from node_rec; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment