MySql获取树型结构的所有子节点

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

stackoverflow的解决方案,亲测有效:

SELECT * FROM person WHERE department IN
(SELECT department_id FROM department WHERE department_id =
UNION
(SELECT department_id FROM
(SELECT * FROM department ORDER BY parent_id,department_id) depart_sorted,
(SELECT @pv := ) initialisation
WHERE find_in_set(parent_id,@pv)
AND length(@pv := concat(@pv,',',department_id))));

sql:

CREATE TABLE department
(
id BIGINT() PRIMARY KEY NOT NULL AUTO_INCREMENT,
department_id INT() NOT NULL,
parent_id INT(),
department_level INT() NOT NULL,
department_name VARCHAR(),
attend_group VARCHAR()
);