如何在MySQL中获取指定根节点对应树形结构的叶子节点

Got it, let's solve this problem. You’ve got two separate hierarchical trees stored in an adjacency list table, and you need to fetch only the leaf nodes for a specific root node—since the original query you referenced grabs every leaf across all trees, that’s not going to cut it. Here’s how to adjust this for multi-tree scenarios:

Solution for MySQL 8.0+ (Recursive CTE - Recommended) #

This is the cleanest, most scalable approach now that MySQL supports recursive common table expressions (CTEs). We first traverse the entire tree starting from your target root, then filter for nodes that have no child nodes.

WITH RECURSIVE tree_nodes AS (
    -- Anchor: Start with your target root node
    SELECT id, category, parent_id
    FROM category
    WHERE id = 1 -- Replace with your desired root ID (e.g., 100 for the second tree)
    UNION ALL
    -- Recursive step: Fetch all child nodes of the previously retrieved nodes
    SELECT c.id, c.category, c.parent_id
    FROM category c
    INNER JOIN tree_nodes tn ON c.parent_id = tn.id
)
-- Filter for leaves: nodes in the tree that have no children
SELECT tn.id, tn.category
FROM tree_nodes tn
LEFT JOIN category c ON tn.id = c.parent_id
WHERE c.id IS NULL;

How this works: #

• The tree_nodes CTE first selects your specified root node, then recursively pulls in all its child nodes, grandchild nodes, and so on until the entire tree is captured.
• The final query joins this CTE back to the original table to identify nodes that have no children (these are your leaf nodes).

Alternative for MySQL 5.x (No Recursive CTE Support) #

If you’re working with an older MySQL version that doesn’t support recursive CTEs, you can use a nested subquery approach to first fetch all nodes in the target tree, then filter for leaves. Note this works best for trees with a known, finite depth:

-- Fetch leaf nodes for root ID = 1 (replace with your root ID)
SELECT t1.id, t1.category
FROM category t1
-- First confirm t1 is part of the target tree
WHERE EXISTS (
    SELECT 1
    FROM category t2
    WHERE t2.id = t1.id
    UNION ALL
    SELECT t3.parent_id FROM category t3 WHERE t3.id = t2.id
    UNION ALL
    SELECT t4.parent_id FROM category t4 WHERE t4.id = t3.id
    -- Add more UNION ALL clauses if your tree is deeper than 3 levels
) AND t1.parent_id IN (
    -- Build out the hierarchy step-by-step
    SELECT id FROM category WHERE parent_id = 1
    UNION ALL
    SELECT id FROM category WHERE parent_id IN (SELECT id FROM category WHERE parent_id = 1)
    -- Add more levels here to match your tree's depth
)
-- Filter out nodes that have children (keep only leaves)
AND NOT EXISTS (
    SELECT 1 FROM category t5 WHERE t5.parent_id = t1.id
);

Example Results: #

• If you set the root ID to 1, the query will return leaves: 3 (TUBE), 4 (LCD), 5 (PLASMA), 8 (FLASH), 9 (CD PLAYERS), 10 (2 WAY RADIOS)
• If you set the root ID to 100, the query will return leaves: 300 (TUBE), 400 (LCD), 500 (PLASMA), 800 (FLASH), 900 (CD PLAYERS), 1000 (2 WAY RADIOS)

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