Now that 3.8.3 is officially out, we can all play with these nice little common
table expressions! Yeah!
So, while solving sudoku puzzles is all fine and dandy, the bread and butter of
recursive queries is more along the lines of plain, old hierarchies.
So, let create one:
select 'A' as node, null as parent union all
select 'B' as node, 'A' as parent union all
select 'C' as node, 'B' as parent union all
select 'D' as node, 'C' as parent
A simple hierarchy, each node with one parent, the root node without one: A → B
→ C → D. Nice and easy.
Let recurse!
with
DataSet
as
(
select 'A' as node, null as parent union all
select 'B' as node, 'A' as parent union all
select 'C' as node, 'B' as parent union all
select 'D' as node, 'C' as parent
),
Hierarchy( node, parent, level, path )
as
(
select DataSet.node,
DataSet.parent,
1 as level,
' → ' || DataSet.node as path
from DataSet
where DataSet.parent is null
union all
select DataSet.node,
DataSet.parent,
Hierarchy.level + 1 as level,
Hierarchy.path || ' → ' || DataSet.node as path
from Hierarchy
join DataSet
on DataSet.parent = Hierarchy.node
)
select *
from Hierarchy
order by path;
node|parent|level|path
A||1| → A
B|A|2| → A → B
C|B|3| → A → B → C
D|C|4| → A → B → C → D
Beautiful. For each node, we get its level and full path, recursively. And
that’s all there is to it. Very nice.
Recursive or not, common table expressions are your friend. Use them ☺
N.B.
One word of caution about circular recursion though… as it stands, SQLite
doesn’t have any build-in mechanism to detect circularity… and will happily get
into a funk and run forever if given the opportunity… so… watch out…
Little demonstration:
with
DataSet
as
(
select 'A' as node, 'A' as parent
),
Hierarchy( node, parent, level, path )
as
(
select DataSet.node,
DataSet.parent,
1 as level,
' → ' || DataSet.node as path
from DataSet
union
select DataSet.node,
DataSet.parent,
Hierarchy.level + 1 as level,
Hierarchy.path || ' → ' || DataSet.node as path
from Hierarchy
join DataSet
on DataSet.parent = Hierarchy.node
)
select *
from Hierarchy
order by path;
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
