MySql 获取树形结构所有子节点
表数据如下:
SELECT
*
FROM
zpermission
WHERE
id IN (
SELECT
id
FROM
zpermission
WHERE
parentId = 1
UNION
(
SELECT
id
FROM
(
SELECT
id
FROM
zpermission
ORDER BY
parentId,
id
) depart_sorted,
(SELECT @pv := 1) initialisation
WHERE
find_in_set(parentid ,@pv)
AND length(@pv := concat(@pv, ',', id))
)
);
Oracle树形结构数据查询
表数据如下:
SELECT
*
FROM
room START WITH ID = 2 CONNECT BY PID = PRIOR ID
SqlServer 树形结构查询
用标准sql的with实现(sql2005以上肯定支持,sql2000不清楚是否支持):
with subqry(id,name,pid) as (
id,name,pid zpermission id = 5 --指定id
union all
z.id,z.name,z.pid zpermission z,subqry z.pid = subqry.id
)
select * from subqry;