I'm currently having some trouble understanding and writing recursive queries. I understand that recursive queries are used to search through hierarchies of information, but I haven't found a simple solution online that can travel up a hierarchy. For example, let's say that I have a relation that models a family tree:
create table family_tree (
child varchar(10)
parent varchar(10)
);
If I wanted to write a recursive query that travelled up this family tree, collecting all parents until origin, how should I go about this?
Thanks in advance.
解决方案
You can use connect by clause.
In your case, SQL might look like:
select child, parent, level
from family_tree
connect by prior parent = child