查询指定部门树

WITH RECURSIVE dept_tree AS (
    SELECT dept_id, parent_id, dept_name, del_flag
    FROM sys_dept
    WHERE dept_id = <部门id>
    UNION ALL
    SELECT d.dept_id, d.parent_id, d.dept_name, d.del_flag
    FROM sys_dept d
    INNER JOIN dept_tree dt ON d.parent_id = dt.dept_id
)
-- 显示部门树的信息
SELECT *
FROM dept_tree
ORDER BY parent_id, dept_id;

更新指定部门树

删除指定部门树：

-- 递归查询找到所有子部门的dept_id
WITH RECURSIVE dept_tree AS (
    SELECT dept_id
    FROM sys_dept
    WHERE dept_id = <部门id>
    UNION ALL
    SELECT d.dept_id
    FROM sys_dept d
    INNER JOIN dept_tree dt ON d.parent_id = dt.dept_id
)
-- 将查询到的部门及其子部门的del_flag设为1
UPDATE sys_dept
SET del_flag = 1
WHERE dept_id IN (SELECT dept_id FROM dept_tree);