SQLite递归查询：依据父ID填充空TYPE列的实现需求

以下是满足需求的SQL查询，使用SQLite的递归CTE（Common Table Expression）实现向上递归查找父节点的非空TYPE值：

WITH RECURSIVE resolved_types AS (
    -- 锚点：直接保留非空TYPE的记录，以及无父节点的空TYPE记录
    SELECT
        ID,
        TYPE,
        PARENT_ID,
        TYPE AS resolved_type
    FROM your_table
    WHERE TYPE IS NOT NULL OR PARENT_ID IS NULL

    UNION ALL

    -- 递归：针对空TYPE且有父节点的记录，继承父节点的已解析TYPE
    SELECT
        t.ID,
        t.TYPE,
        t.PARENT_ID,
        rt.resolved_type
    FROM your_table t
    JOIN resolved_types rt ON t.PARENT_ID = rt.ID
    WHERE t.TYPE IS NULL AND t.PARENT_ID IS NOT NULL
)
-- 确保每个ID只返回一条最终解析结果
SELECT
    ID,
    COALESCE(resolved_type, TYPE) AS TYPE,
    PARENT_ID
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN resolved_type IS NOT NULL THEN 0 ELSE 1 END) AS rn
    FROM resolved_types
) ranked
WHERE rn = 1
ORDER BY ID;

代码逻辑说明 #

1. 递归CTE定义：

   • 锚点部分先筛选出无需递归的记录：TYPE本身非空的，或者TYPE为空但没有父节点的（如ID=1006），直接保留原TYPE作为resolved_type。
   • 递归部分处理TYPE为空且存在父节点的记录，通过PARENT_ID关联到已经完成解析的父节点，继承父节点的resolved_type，直到找到第一个非空的TYPE值。

2. 去重与结果筛选：

   • 递归过程中，一个节点可能会被多次关联（比如ID=1005会先关联1002，再通过1002关联到1000），因此用ROW_NUMBER()按ID分组，优先选择带有有效resolved_type的记录，确保每个ID只返回一条最终结果。

3. 结果输出：

   • 使用COALESCE(resolved_type, TYPE)确保优先使用递归解析得到的TYPE值，没有解析结果的则保留原TYPE（如ID=1006）。

替换查询中的your_table为你的实际表名即可执行，执行结果将完全匹配你给出的预期输出。

内容的提问来源于stack exchange，提问作者Isaac Sullivan