SQL递归查询树结构语法

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

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

mysql

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)

postgresql

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;