对于树形结构数据，经常会有递归查询的需求，语法如下：

注意替换sql中的表名和id入参

sql_3">mysql

sql">WITH RECURSIVE node_cte (id, parent_id) 
AS (SELECT id, parent_id FROM my_table -- 表名，换成要查询的名称WHERE id = :id -- 根节点id，换成要查询的根节点idUNION ALLSELECT t.id, t.parent_id FROM my_table tJOIN node_cte nc ON t.parent_id = nc.id
)
SELECT * 
FROM my_table 
WHERE id IN (SELECT id FROM node_cte)

sql_20">postgresql

sql">WITH RECURSIVE node_cte AS (SELECT id, parent_idFROM my_table -- 表名，换成要查询的名称WHERE id = :id -- 根节点id，换成要查询的根节点idUNION ALLSELECT t.id, t.parent_idFROM my_table t -- 表名，换成要查询的名称JOIN node_cte nc ON t.parent_id = nc.id
)
SELECT *
FROM sp_permission
WHERE id IN (SELECT id FROM node_cte) order by id;