Examples of implementations of SQLite using CTE and recursion

1. Multi-level CTE query example.

 
 
with cte as
(
   select pageid 
   from cm_bookpage
) , cte2 as
(
  select pageid, 2 as content from cte
)
select * from cte2

2. CTE recursive query article header level, 3872 a leaf node, to query out all the parent directory and sort back.

 
with cte as
(
  select pageid,bookid,title,parentpageid,1 as orderid
  from cm_bookpage 
  where pageid = 3872        
  union all  
  select a.pageid,a.bookid,a.title,a.parentpageid,(cte.orderid+1) as orderid
  from cm_bookpage a  
    inner join cte       
      on a.pageid = cte.parentpageid  
)
select *
from cte
order by orderid desc 

Examples of implementations of SQLite using CTE and recursion