mysql组织架构寻找所有上级组织
有一个组织表:
输入一个组织id,找出他所有的上级组织
比如,查找id为6的组织的所有父级组织,那么应该出来2和1
比如,查找id为5的组织的所有父级组织,那么应该出来3和1
如果有更深层级的组织,则需要将所有层级的组织全部找出来
SELECT _id AS org_id, parent_id, lvl
FROM (
SELECT @orgId AS _id
, (
SELECT @orgId := parent_id
FROM dept_info
WHERE id = _id
) AS parent_id,
@l := @l + 1 AS lvl
FROM (
SELECT @orgId := 6, @l := 0
) vars, dept_info h
WHERE @orgId IS NOT NULL
) T1
WHERE T1.parent_id IS NOT NULL
ORDER BY lvl DESC
运行结果:
当前,自己也出来了
我们修改一下:
SELECT _id AS org_id, parent_id, lvl
FROM (
SELECT @orgId AS _id
, (
SELECT @orgId := parent_id
FROM dept_info
WHERE id = _id
) AS parent_id,
@l := @l + 1 AS lvl
FROM (
SELECT @orgId := 6, @l := 0
) vars, dept_info h
WHERE @orgId IS NOT NULL
) T1
WHERE T1.parent_id IS NOT NULL AND T1.parent_id != 0
ORDER BY lvl DESC
运行结果:
加了一个条件之后,除了自己,自己所有的父级层级的组织全部出来了
转自快乐的海盗