下面是一种使用递归的方法：

• “* 基准步骤 *"：从tab 1收集信息
• “* 递归步骤 *"：在匹配cte.ParentIDFromTable2 = tab2.id时将步骤n-1的cte与tab 2连接。通过在开头连接相同的字符，使>前缀名称的描述可识别。

递归完成后，您可以：

• 用FIRST_VALUE收集第一个相关描述，并将记录排序到...
• 使用GROUP_CONCAT对姓名进行聚合，以获取您的血统
• 最后与tab 3连接以收集祖先信息

每一个都在子查询中执行。

WITH RECURSIVE cte AS (
    SELECT id AS firstId, name AS firstName, 
           NULL AS lastId, name, ParentIDFromTable2, Weight, Height,
           Description, LocationIDFromTable3 AS Location
    FROM tab1

    UNION ALL
  
    SELECT cte.firstId, cte.firstName, 
           tab2.id, TRIM(tab2.Name, '>'), tab2.GrandParentID, NULL, NULL, 
           CASE WHEN SUBSTR(tab2.Name, 0, 2)='>' 
                THEN '>' || tab2.Description
           END AS Description, NULL
    FROM       cte 
    INNER JOIN tab2
            ON cte.ParentIDFromTable2 = tab2.id
), cte2 AS (
    SELECT firstId, firstName, Weight, Height, name, lastId,
           FIRST_VALUE(Description) OVER(
               PARTITION BY firstId 
               ORDER     BY Description IS NULL,
                            SUBSTR(name, 0, 2)='>' DESC, 
                            lastid DESC
           ) AS Description, 
           Location
    FROM cte 
    ORDER BY firstId, ParentIDFromTable2
), cte3 AS (
    SELECT firstId                 AS id, 
           firstName               AS name,
           MAX(Weight)             AS weight,
           MAX(Height)             AS height,
           MIN(Description)        AS description,
           MAX(Location)           AS location,
           GROUP_CONCAT(name, '/') AS lineage
    FROM cte2 
    GROUP BY firstId, 
             firstName
)
SELECT cte3.id, 
       cte3.name, 
       cte3.weight, 
       cte3.height, 
       TRIM(cte3.description, '>') AS description, 
       tab3.name || '/' || cte3.lineage AS lineage
FROM       cte3
INNER JOIN tab3
        ON tab3.id = cte3.location

输出：

| id|姓名|重量|高度|描述|世系|
| - -----|- -----|- -----|- -----|- -----|- -----|
| 一个|杰克|一百八十|一百八十三|Des5| P1/Adam/Zack/Noah/Jacob/Jeff/Jack|
| 2|麻雀|二百一十|一百六十九|Des2| P1/亚当/扎克/诺亚/麻雀|
| 3|约翰|三百五十|二百一十|Des5| P1/Adam/Zack/Noah/Jacob/Jeff/Drake/John|
| 4|吉尔|一百一十|一百四十|T1描述4| P1/Adam/Jill|
| 5个|朱丽叶|一百二十二|一百五十|Des2| P1/亚当/扎克/诺亚/坎耶/朱丽叶|
查看演示here。

EDIT：尝试删除一些要在末尾添加的列如下：

• 在递归CTE中保留所有需要更新的列，
• 添加上次连接操作以恢复丢失的字段

在下面的示例中，我们将删除字段“weight”和“height”，以便在最后一步中添加它们。

WITH RECURSIVE cte AS (
    SELECT id AS firstId, name AS firstName, 
           NULL AS lastId, name, ParentIDFromTable2,
           Description, LocationIDFromTable3 AS Location
    FROM tab1

    UNION ALL
  
    SELECT cte.firstId, cte.firstName, 
           tab2.id, TRIM(tab2.Name, '>'), tab2.GrandParentID, 
           CASE WHEN SUBSTR(tab2.Name, 0, 2)='>' 
                THEN '>' || tab2.Description
           END AS Description, NULL
    FROM       cte 
    INNER JOIN tab2
            ON cte.ParentIDFromTable2 = tab2.id
), cte2 AS (
    SELECT firstId, firstName, name, lastId,
           FIRST_VALUE(Description) OVER(
               PARTITION BY firstId 
               ORDER     BY Description IS NULL,
                            SUBSTR(name, 0, 2)='>' DESC, 
                            lastid DESC
           ) AS Description, 
           Location
    FROM cte 
    ORDER BY firstId, ParentIDFromTable2
), cte3 AS (
    SELECT firstId                 AS id, 
           firstName               AS name,
           MIN(Description)        AS description,
           MAX(Location)           AS location,
           GROUP_CONCAT(name, '/') AS lineage
    FROM cte2 
    GROUP BY firstId, 
             firstName
)
SELECT cte3.id, 
       cte3.name, 
       tab1.weight, 
       tab1.height, 
       TRIM(cte3.description, '>') AS description, 
       tab3.Name || '/' || cte3.lineage AS lineage
FROM       cte3
INNER JOIN tab1 
        ON tab1.id = cte3.id
INNER JOIN tab3
        ON tab1.LocationIDFromTable3 = tab3.id

查看演示here。

你需要一个递归的CTE和聚合，利用SQLite的裸列特性：

WITH cte AS (
  SELECT id, Name, Weight, Height, Description, Name Lineage, ParentIDFromTable2, LocationIDFromTable3, 
             false flag, 0 level 
  FROM Table1
  UNION ALL
  SELECT c.id, c.Name, c.Weight, c.Height, 
         CASE WHEN NOT flag AND t2.Name LIKE '>%' THEN t2.Description ELSE c.Description END, 
         TRIM(t2.Name, '>') || '/' || c.Lineage,
         t2.GrandParentID,
         c.LocationIDFromTable3,
         flag OR t2.Name LIKE '>%',
         c.level + 1
  FROM Table2 t2 INNER JOIN cte c 
  ON c.ParentIDFromTable2 = t2.id
)
SELECT t.id, t.Name, t.Weight, t.Height, t.Description, 
       t3.Name || '/' || t.Lineage Lineage 
FROM (SELECT *, MAX(level) FROM cte GROUP BY id) t 
INNER JOIN Table3 t3
ON t3.id = t.LocationIDFromTable3
GROUP BY t.id;

如果char '>'可能位于Table2中Name的值内的任何位置，而不仅仅是在开始处，则将t2.Name LIKE '>%'替换为INSTR(t2.Name, '>')。
参见demo。