SQL 递归查询：获取三级部门数据并按顺序排序

可以使用递归查询（Recursive Query）来实现获取三个级别的部门数据，并按照指定的排序方式进行排序。假设部门表的结构如下：\n\nsql\nCREATE TABLE departments (\n department_id INT PRIMARY KEY,\n department_name VARCHAR(100),\n parent_department_id INT\n);\n\n\n以下是一个示例的 SQL 查询语句，可以获取三个级别的部门数据，并按照指定的排序方式进行排序：\n\nsql\nWITH RECURSIVE recursive_cte (department_id, department_name, parent_department_id, level, sort_order) AS (\n SELECT department_id, department_name, parent_department_id, 1 AS level,\n ROW_NUMBER() OVER (ORDER BY department_id) AS sort_order\n FROM departments\n WHERE parent_department_id IS NULL -- 获取第一个一级部门\n UNION ALL\n SELECT d.department_id, d.department_name, d.parent_department_id, r.level + 1,\n ROW_NUMBER() OVER (ORDER BY d.department_id) AS sort_order\n FROM departments AS d\n INNER JOIN recursive_cte AS r ON d.parent_department_id = r.department_id -- 获取子部门\n WHERE r.level < 2 -- 获取第一个一级部门及其子部门\n UNION ALL\n SELECT d.department_id, d.department_name, d.parent_department_id, r.level + 1,\n ROW_NUMBER() OVER (ORDER BY d.department_id) AS sort_order\n FROM departments AS d\n INNER JOIN recursive_cte AS r ON d.parent_department_id = r.department_id -- 获取第二个一级部门及其子部门\n WHERE r.level = 2 -- 获取第二个一级部门及其子部门\n)\nSELECT department_id, department_name, parent_department_id, level, sort_order\nFROM recursive_cte\nORDER BY sort_order;\n\n\n这个查询语句中使用了递归公共表表达式（Recursive Common Table Expression，CTE）来实现递归查询。在递归 CTE 中，首先选择第一个一级部门，并将其作为初始结果集。然后使用联接操作将子部门与父部门进行连接，逐级获取子部门数据，直到达到指定的级别。最后，通过 ROW_NUMBER() 函数对结果进行排序，确保按照指定的排序方式排序。\n\n请根据实际情况，调整查询语句中的表名、列名和排序方式。