冷门 MYSQL 多级分类递归查询 WITH RECURSIVE， CTE（Common Table Expression）

 $job = query('job',"id='$staff[jobId]'");
                $and .= " and (
                            TB.stid IN (
                SELECT        stid 
                FROM        `staff` 
                WHERE        jobId IN (
                    SELECT  id 
                    FROM  job 
                    WHERE  departmentId IN (
                            WITH RECURSIVE subordinates AS (
                            SELECT id, pid 
                            FROM department 
                            WHERE id = '$job[departmentId]' UNION ALL
                            SELECT e.id, e.pid 
                            FROM department e
                                INNER JOIN subordinates s ON e.pid = s.id 
                            ) SELECT    id 
                        FROM  subordinates 
                        ))) 
                OR manager IN (
                SELECT        stid 
                FROM        `staff` 
                WHERE        jobId IN (
                    SELECT  id 
                    FROM  job 
                    WHERE  departmentId IN (
                            WITH RECURSIVE subordinates AS (
                                SELECT id, pid 
                                FROM department 
                                WHERE id = '$job[departmentId]' UNION ALL
                                SELECT e.id, e.pid 
                                FROM department e
                                INNER JOIN subordinates s ON e.pid = s.id 
                            ) SELECT  id  FROM  subordinates 
                        ) 
                    ) 
                )
               ) ";

这个MySQL语句使用了递归CTE（Common Table Expression）来查询部门及其所有子部门。让我来详细解释：

语句结构分析

1. 递归CTE定义

WITH RECURSIVE subordinates AS (
    -- ...
)

• 使用 WITH RECURSIVE 创建一个递归的公用表表达式
• subordinates 是CTE的名称

2. 初始查询（锚点）

SELECT id, pid 
FROM department 
WHERE id = 'adacd6795f3b51d1'

• 这是递归的起点，选择ID为 'adacd6795f3b51d1' 的部门
• 获取该部门的 id 和 pid（父级ID）

3. 递归查询部分

UNION ALL
SELECT e.id, e.pid 
FROM department e
    INNER JOIN subordinates s ON e.pid = s.id

• 使用 UNION ALL 连接初始查询和递归查询

• 递归地查找所有子部门：

  • 从 department 表中选择记录（别名为 e）
  • 通过 INNER JOIN 连接到CTE本身（别名为 s）
  • 连接条件是 e.pid = s.id，即查找父ID等于当前CTE中ID的记录

4. 最终查询

SELECT id 
FROM subordinates

• 从递归CTE结果中选择所有部门的 id

功能说明

这个查询的作用是：

1. 从指定ID的部门开始（'adacd6795f3b51d1'）
2. 递归查找该部门的所有下级子部门（直接和间接的子部门）
3. 返回包含原始部门及其所有子部门的ID列表

这是一种典型的组织架构查询，用于获取某个部门及其所有下属部门的完整层级结构。

核心部分

WITH RECURSIVE subordinates AS (
                            SELECT id, pid 
                            FROM department 
                            WHERE id = 'a7df92713102e4b6' UNION ALL
                            SELECT e.id, e.pid 
                            FROM department e
                                INNER JOIN subordinates s ON e.pid = s.id 
                            ) SELECT    id 
                        FROM  subordinates 

subordinates 是临时表名，可以替换

简化SQL，分步查询

    /**
     * 获取当前部门及所有子部门的ID
     * @param $departmentId
     * @return array
     */
    public static function getAllChildDepartments($departmentId)
    {
        $sql = "WITH RECURSIVE subordinates AS (
                    SELECT id, pid 
                    FROM department 
                    WHERE id = '$departmentId' UNION ALL
                    SELECT e.id, e.pid 
                    FROM department e
                    INNER JOIN subordinates s ON e.pid = s.id 
                ) SELECT id FROM subordinates";
        $data = self::sqlFind($sql);
        return array_column($data, 'id');
    }

                $job = query('job',"id='$staff[jobId]'");
                //查询该职位所在部门的所有子部门
                $allDepartmentIds = departmentClass::getAllChildDepartments($job['departmentId']);
                $jobs = find([
                    "table" => "job",
                    "where" => "departmentId IN ('".implode("','",$allDepartmentIds)."')",
                    "field" => 'id',
                ]);
                //这些所有子部门的职位
                $jobIds = array_column($jobs['array'],'id');
                //查询这些职位的员工ID
                $staffs = find([
                    "table" => "staff",
                    "where" => "jobId IN ('".implode("','",$jobIds)."')",
                    "field" => 'stid',
                ]);
                $staffIds = array_column($staffs['array'],'stid');
                $and .= " and (TB.stid IN ('".implode("','",$staffIds)."') OR TB.manager IN ('".implode("','",$staffIds)."'))";
           

版权属于：Joyber

本文链接：https://blog.qqvbc.com/default/1332.html

转载时须注明出处及本声明