部门及子部门
(
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
)