冷门 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
功能说明
这个查询的作用是：
- 从指定ID的部门开始（'adacd6795f3b51d1'）
- 递归查找该部门的所有下级子部门（直接和间接的子部门）
- 返回包含原始部门及其所有子部门的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)."'))";