SELECT T2.id, T2.project_name, T1.lvl as level
FROM (
SELECT
@rAS _id,(SELECT @r:= project_parent_id FROM wk_project_item WHERE id = _id)AS project_parent_id,@l:=@l+1 AS lvl
FROM
(SELECT @r:= #{projectId},@l:=0) vars,
wk_project_item h
WHERE @r!=0) T1
JOIN wk_project_item T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
获取所有下级
SELECT
ID.LEVEL,
DATA.id , DATA.project_name as projectName
FROM
(
SELECT
@idsAS _ids,( SELECT @ids:=GROUP_CONCAT( id ) FROM wk_project_item WHEREFIND_IN_SET(project_parent_id,@ids))AS cids,@l:=@l+1 AS LEVEL
FROM
wk_project_item,( SELECT @ids:= #{projectId},@l:=0) b
WHERE
@ids IS NOT NULL
) ID,
wk_project_item DATA
WHEREFIND_IN_SET( DATA.id, ID._ids )
ORDER BY
LEVEL