SQL 获取当前部门、部门人员，以及它的子级

部门及子部门

(
	WITH RECURSIVE dept AS (
			SELECT id, jgjc as name, jgbm as jgbh, sjjgbh, 'JG' as type FROM t_jgxx
			WHERE jgbm = '111111' and isdel = 0
			UNION ALL
			SELECT d.id, d.jgjc as name, d.jgbm as jgbh, d.sjjgbh, 'JG' as type FROM t_jgxx d
			JOIN dept ON d.sjjgbh = dept.jgbh WHERE isdel = 0
	)
	SELECT * FROM dept
)

部门下的人员

(
	WITH RECURSIVE dept AS (
		SELECT id, jgjc as name, jgbm as jgbh, sjjgbh, 'JG' as type FROM t_jgxx
		WHERE jgbm = '111111' and isdel = 0
		UNION ALL
		SELECT d.id, d.jgjc as name, d.jgbm as jgbh, d.sjjgbh, 'JG' as type FROM t_jgxx d
		JOIN dept ON d.sjjgbh = dept.jgbh WHERE isdel = 0
	)
	SELECT bh as id,yhmc as name,dwbh as jgbh,'' as sjjgbh,'RY' as type FROM dept
	JOIN t_yhxx ON dept.jgbh = t_yhxx.dwbh WHERE isdel = 0  
)

合并部门和人员

(
	WITH RECURSIVE dept AS (
			SELECT id, jgjc as name, jgbm as jgbh, sjjgbh, 'JG' as type FROM t_jgxx
			WHERE jgbm = '111111' and isdel = 0
			UNION ALL
			SELECT d.id, d.jgjc as name, d.jgbm as jgbh, d.sjjgbh, 'JG' as type FROM t_jgxx d
			JOIN dept ON d.sjjgbh = dept.jgbh WHERE isdel = 0
	)
	SELECT * FROM dept
)

UNION ALL

(
	WITH RECURSIVE dept AS (
		SELECT id, jgjc as name, jgbm as jgbh, sjjgbh, 'JG' as type FROM t_jgxx
		WHERE jgbm = '111111' and isdel = 0
		UNION ALL
		SELECT d.id, d.jgjc as name, d.jgbm as jgbh, d.sjjgbh, 'JG' as type FROM t_jgxx d
		JOIN dept ON d.sjjgbh = dept.jgbh WHERE isdel = 0
	)
	SELECT bh as id,yhmc as name,dwbh as jgbh,'' as sjjgbh,'RY' as type FROM dept
	JOIN t_yhxx ON dept.jgbh = t_yhxx.dwbh WHERE isdel = 0  
)