获取部门的祖籍列表(若依,用于数据同步)
【代码】获取部门的祖籍列表(若依,用于数据同步)
·
获取部门的祖籍列表(若依,用于数据同步)
获取祖籍列表,包括自己
select GROUP_CONCAT(A.dept_id SEPARATOR ',') as ancestors from (SELECT T2.dept_id, T2.dept_name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM sys_dept WHERE dept_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 173697, @l := 0) vars,
sys_dept h
WHERE @r <> 0) T1
JOIN sys_dept T2
ON T1._id = T2.dept_id
ORDER BY T1.lvl DESC ) A;
获取祖籍列表,不包括自己
SELECT
CONCAT(
'0,',
GROUP_CONCAT( A.parent_id SEPARATOR ',' )) as ancestors
FROM
(
SELECT
_id AS org_id,
parent_id,
lvl
FROM
(
SELECT
@orgId AS _id,
( SELECT @orgId := parent_id FROM sys_dept WHERE dept_id = _id ) AS parent_id,
@l := @l + 1 AS lvl
FROM
( SELECT @orgId :=230455 , @l := 0 ) vars,
sys_dept h
WHERE
@orgId IS NOT NULL
) T1
WHERE
T1.parent_id IS NOT NULL
AND T1.parent_id != 0
ORDER BY
lvl DESC
) A;
更多推荐
已为社区贡献1条内容
所有评论(0)