SELECT
a
.
id
,
a
.
pid
,
a
.
name
,
0
AS
loclevel
FROM
user
a
where
a
.
id
=
'47dbe7d9-96bf-414e-b318-cb288b0f381b'
UNION
ALL
SELECT
a
.
id
,
a
.
pid
,
a
.
name
,
loclevel
+
1
FROM
user
a
INNER
JOIN
locs p
ON
a
.
pid
=
p
.
id
SELECT
*
FROM
locs
sql语句，根据id查询所有子级，包括当前父级（无限层级）WITH RECURSIVE locs(id,pid,name)AS(SELECT a.id,a.pid,a.name,0 AS loclevel FROM user awhere a.id='47dbe7d9-96bf-414e-b318-cb288b0f381b'UNION ALLSELECT a.id,a.pid,a.name,loclevel+1 FROM user aINNER JOIN locs p ON a.pid
t1.
id
, t1.parent_
id
,
t2.*,
IF(FIND_IN_SET(t1.parent_
id
, @p
id
s) > 0, @p
id
s := CONCAT(@p
id
s, ',', t1.
id
), 0) AS isChild
FROM (
SELECT
id
, parent_
id
FROM sy
t1.*,
t2.*,
IF(FIND_IN_SET(parent_
id
, @p
id
s) > 0, @p
id
s := CONCAT(@p
id
s, ',',
id
), '0') AS isChild
FROM (
SELECT * FROM rule_enti
SELECT * FROM department
WHERE company_
id
= 'your_company_
id
'
ORDER BY parent_
id
ASC,
id
ASC;
其中，`department` 表是您存储部门信息的表，`company_
id
` 是您要
查询
的公司
id
，`parent_
id
` 是每个部门的父部门
id
，`
id
` 是每个部门的
id
。使用 `ORDER BY` 子句，首先按照 `parent_
id
` 递增排序，然后按照 `
id
` 递增排序，可以得到按照
层级
递增排序的部门列表。
