递归查询机构树

Enriquze

于 2024-06-22 11:46:59 发布

阅读量110

点赞数 2

文章标签：数据库 sql

本文链接：https://blog.csdn.net/Enriquze/article/details/139879987

版权

SELECT t5.peopleName, // 人员姓名
	   t4.unitGuid as departmentId // 部门id
FROM
	cmu_property_people t5 // 人员表
	INNER JOIN (
	SELECT
		UnitGuid,
		DepartmentName,
		ischild 
	FROM
		(
		SELECT
			t1.UnitGuid,
			t1.DepartmentName,
		IF
			( find_in_set( ParentDepartmentId, @pids ) > 0, @pids := concat( @pids, ',', UnitGuid ), 0 ) AS ischild 
		FROM
			( SELECT UnitGuid, ParentDepartmentId, DepartmentName FROM sys_department t ORDER BY ParentDepartmentId, UnitGuid ) t1,
			( SELECT @pids := 'fe9244ad-88c8-4a04-806f-17d47396c18b' ) t2 //这个是传入的部门id
		) t3 
	WHERE
		ischild != '0'  //到这儿是查子部门的
	OR unitguid = 'fe9244ad-88c8-4a04-806f-17d47396c18b'  //到这儿是查当前部门的
	) t4 ON t5.DepartmentId = t4.UnitGuid