查询树中的父母

Question

我有数据库表任务如下。

SELECT _id,name,parentId FROM Task; 

_id   name     parentId 
---------- -------------------- ---------- 
4   Software Development    
5   Machine Learning     
6   Programing   4   
7   Build System   4   
8   version control  4   
9   Android App Developm 4   
10   Udacity Cource  5   
11   Mathematics   5   
12   skLearn docs   5   
13   problem solving  6   
14   breakdown    13   
15   language    6   
16   c      15   
17   c++     15   
18   java     15   
19   kotlin    15   
20   gradle    7   
21   bazel     7   
22   git     8   
23   svn     8   

有一个表使用_id（主键）和parentId的与各自的父任务有关的所有任务及其子任务。

例如任务名'java'具有_id = 18且parentId = 15意味着'java'是_id = 15的子任务，其是'语言'。

再次'语言'有_id = 15和parentId = 6意味着'language'是_id = 6的子任务，它是'编程'。

同样的'程序'是'软件开发'的子任务。

而'软件开发'是空的子任务。

所以我需要一个查询，其输出如下输出_id = 18（即'java'），它是父任务的父母，父任务的父母...到子任务的顶部。

 
_id  name    parentId 
4 Software Development null 
6 Programing    4 
15 language    6 
18 java     15 

目前，我可以在一个循环中使用4个查询来获取这个输出。

SELECT _id,name,parentId FROM task WHERE _id = 18 

在下一迭代_id将是从上述查询

这是耗时的，所以我们可以对这个更好的解决方案的输出的parentId的值。

Answer 1

在一棵树上去了，需要一个递归common table expression：

WITH RECURSIVE parents(id, name, parentid, level) AS (
    SELECT _id, name, parentid, 1 
    FROM Task 
    WHERE _id = 18 

    UNION ALL 

    SELECT Task._id, Task.name, Task.parentid, level + 1 
    FROM Task 
    JOIN parents ON Task._id = parents.parentid 
) 
SELECT id, name, parentid 
FROM parents 
ORDER BY level DESC; 

该系统为Android Lollipop（API级别21）之前不被支持。

Answer 2

我也有这样的业务，我结合sql与java代码来解决这个问题。 只是这样的：

public ArrayList<String> getRecursiveReverse(String parentId) throws Exception { 
    StringBuffer sqlObject = new StringBuffer(); 
    sqlObject.append("SELECT T.TABLE_ID "); 
    sqlObject.append("FROM TABLE_NAME T "); 
    sqlObject.append("WHERE 1 = 1 "); 
    sqlObject.append("  AND T.STATUS = 1 "); 
    sqlObject.append("  AND T.PARENT_ID = ? "); 

    Cursor c = null; 
    String[] params = { parentId }; 
    ArrayList<String> listIdArray = new ArrayList<String>(); 
    if (!StringUtil.isNullOrEmpty(parentId)) { 
      listIdArray.add(parentId); 
    } 
    try { 
     c = rawQuery(sqlObject.toString(), params); 
     if (c != null) { 
      if (c.moveToFirst()) { 
       do { 
        String tableId = CursorUtil.getString(c, "TABLE_ID"); 

        ArrayList<String> tempArray = getShopRecursiveReverse(tableId); 
        listIdArray.addAll(tempArray); 
       } while (c.moveToNext()); 
      } 
     } 
    } finally { 
     try { 
      if (c != null) { 
       c.close(); 
      } 
     } catch (Exception e) { 
      MyLog.w(getTAG(), GlobalUtil.getCurrentMethodName(), e); 
     } 
    } 
    return listIdArray; 
}