如何获取SQLite数据库中层级树所有分支的起止节点？

我来帮你搞定这个问题！先假设你的表结构完整定义是这样的（补全你给出的示例）：

CREATE TABLE feat_link (
    ws_id integer,
    source_node varchar(10),
    target_node varchar(10)
);

要拿到每棵树（按ws_id区分）的所有分支起始点和终止点，分两种场景来处理会更清晰：

1. 快速列出所有起点和终点（不关联路径） #

如果你只是需要单独列出每个ws_id下的所有起点、所有终点，不需要明确对应关系，用这两个简单查询就够了：

找所有起始节点 #

起始节点就是在同一棵树里，从来没被其他节点指向的节点（也就是没出现在target_node里的source_node）：

SELECT ws_id, source_node AS start_node
FROM feat_link
GROUP BY ws_id, source_node
EXCEPT
SELECT ws_id, target_node
FROM feat_link
GROUP BY ws_id, target_node;

找所有终止节点 #

终止节点则是在同一棵树里，从来没指向其他节点的节点（也就是没出现在source_node里的target_node）：

SELECT ws_id, target_node AS end_node
FROM feat_link
GROUP BY ws_id, target_node
EXCEPT
SELECT ws_id, source_node
FROM feat_link
GROUP BY ws_id, source_node;

2. 精准匹配每个分支的起止节点（关联完整路径） #

如果需要明确哪个起点对应哪个终点（也就是每条分支的首尾），就得用SQLite的递归CTE来遍历所有路径了，看下面的代码：

WITH RECURSIVE branch_paths AS (
    -- 第一步：先找到所有树的起点（没有父节点的节点），初始化路径
    SELECT
        ws_id,
        source_node AS start_node,
        target_node AS current_node
    FROM feat_link
    WHERE source_node NOT IN (
        SELECT target_node FROM feat_link WHERE ws_id = feat_link.ws_id
    )
    UNION ALL
    -- 第二步：沿着节点关系往下递归遍历，直到走不动为止
    SELECT
        bp.ws_id,
        bp.start_node,
        fl.target_node AS current_node
    FROM branch_paths bp
    JOIN feat_link fl 
        ON bp.current_node = fl.source_node 
        AND bp.ws_id = fl.ws_id
)
-- 第三步：筛选出每个路径的终点（没有后续节点的记录），再补上孤立节点的情况
SELECT DISTINCT
    ws_id,
    start_node,
    current_node AS end_node
FROM branch_paths
WHERE current_node NOT IN (
    SELECT source_node FROM feat_link WHERE ws_id = branch_paths.ws_id
)
UNION
SELECT
    ws_id,
    source_node AS start_node,
    source_node AS end_node
FROM feat_link
WHERE source_node NOT IN (
    SELECT target_node FROM feat_link WHERE ws_id = feat_link.ws_id
)
AND source_node NOT IN (
    SELECT source_node FROM feat_link WHERE ws_id = feat_link.ws_id AND target_node IS NOT NULL
);

代码为啥这么写？ #

• 递归的锚点部分：先把所有“根节点”（没被其他节点指向的）找出来，作为每条路径的起点。
• 递归遍历部分：从起点出发，跟着source_node→target_node的关系一步步往下走，把所有路径都遍历一遍。
• 最终筛选：只留下每条路径的最后一个节点（也就是没有子节点的终点），同时补上那些孤立的节点——这类节点自己就是自己的起点和终点。

举个例子测试下 #

假设你表中有这些测试数据：

INSERT INTO feat_link VALUES
(1, 'A', 'B'),
(1, 'B', 'C'),
(1, 'A', 'D'),
(2, 'X', 'Y'),
(2, 'Z', 'Z'); -- 这个是孤立节点

跑上面的递归查询，会得到这样的结果：

完全符合你要的每棵树分支的起止点～

内容的提问来源于stack exchange，提问作者N.Varela