SQL Lite中的递归查询CTE

Question

我有以下表结构。(I am new to SQL Lite)

create table Relations 
(
Code int,
ParentCode int ,
fname text
)
GO
insert into Relations values(1,null,'A');
insert into Relations values(2,null,'B');
insert into Relations values(3,2,'C');
insert into Relations values(4,3,'D');

我想要得到Code =4的初始父元素:即值2 null B

我不知道如何在sqlite中编写递归查询。

Answer 1

是版本问题。这个查询不能工作& &;得到一个语法错误。我从3.7.17版本升级到3.8.7.4版本它工作. .

WITH RECURSIVE
  works(Code,Parent) AS (
   Select Code,ParentCode from Relations a where a.Code=4
    UNION
    SELECT Relations.Code, Relations.ParentCode FROM Relations , works
     WHERE Relations.Code=works.Parent
  )
SELECT * FROM works where Parent is null