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
SELECT * FROM (WITH RECURSIVE GETID(N) AS ( VALUES(‘3‘) UNION SELECT ID FROM TESTTABLE ORG, GETID WHERE ORG.PARENTID=GETID.N ) SELECT ID ,PARENTID, NAME FROM TESTTABLE ORG WHERE ORG.PARENTID IN GETID)
3.根据ID获取所有父ID（parentid）
SELECT * FROM (WITH RECURSIVE GETID(N) AS ( VALUES(‘301010101‘) UNION SELECT PARENTID FROM TESTTABLE ORG, GETID WHERE ORG.ID=GETID.N ) SELECT ID ,PARENTID, NAME FROM TESTTABLE ORG WHERE ORG.ID IN GETID)
原文：https://www.cnblogs.com/yellow3gold/p/15232765.html