SQLite递归

SQLite递归的用法的一些总结（by yellow3gold）：

1.新建表TestTable

CREATE TABLE TestTable (
"ID"  NVARCHAR2(20),
"PARENTID"  NVARCHAR2(20),
"NAME"  NVARCHAR(100),
PRIMARY KEY ("ID")
);

2.插入测试数据

INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (101, 1, 'name101');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (102, 1, 'name102');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (201, 2, 'name201');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (301, 3, 'name301');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (302, 3, 'name302');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (303, 3, 'name303');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (10101, 101, 'name10101');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (30301, 303, 'name30301');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (3010101, 30301, 'name3010101');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (301010101, 3010101, 'name301010101');
INSERT INTO "TestTable" ("ID", "PARENTID", "NAME") VALUES (1010101, 10101, 'name1010101');

3.根据父ID（parentid）获取所有子ID

WITH RECURSIVE
GETID(N) AS (
VALUES('3')
UNION
SELECT ID FROM TESTTABLE T, GETID WHERE T.PARENTID=GETID.N)
SELECT ID ,PARENTID, NAME FROM TESTTABLE T WHERE T.PARENTID IN GETID

 4.根据ID获取所有父ID（parentid）

WITH RECURSIVE
GETID(N) AS (
VALUES ('301010101')
UNION 
SELECT PARENTID FROM TESTTABLE T, GETID WHERE T.ID = GETID.N) 
SELECT ID, PARENTID, NAME FROM TESTTABLE T WHERE T.ID IN GETID