使用cte函数,可以在sql中使用递归来进行查找
WITH RECURSIVE cte AS (
SELECT
id,
parent_id,
1 AS LEVEL
FROM
account
WHERE
id = '1' UNION ALL
SELECT
a.id,
a.parent_id,
dh.LEVEL + 1 AS LEVEL
FROM
account a
INNER JOIN cte dh ON a.parent_id = dh.id
) SELECT
*
FROM
cte dh
WHERE
NOT EXISTS ( SELECT 1 FROM account d WHERE d.parent_id = dh.id )
ORDER BY
LEVEL
可以将所有的叶子节点查找到。如果需要所有的,则可以删掉where的过滤。