在 SQL 中,可以使用递归公共表达式(Recursive Common Table Expressions,简称 CTE)来实现树形查询。递归 CTE 允许我们对具有层次结构或递归关联的数据进行查询。下面是一个实现树形查询的例子:
假设我们有一个部门表(department),其结构如下:
CREATE TABLE department (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES department(id)
);
这个表包含一个主键字段 id
,一个部门名称字段 name
,以及一个外键字段 parent_id
,该字段指向上级部门的 id
。如果部门没有上级部门,则 parent_id
为 NULL。
接下来,我们可以使用递归 CTE 查询该表,以获取部门的层次结构:
WITH RECURSIVE department_cte (id, name, parent_id, level) AS (
SELECT id, name, parent_id, 1
FROM department
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.name, d.parent_id, dept_cte.level + 1
FROM department d
INNER JOIN department_cte dept_cte ON d.parent_id = dept_cte.id
)
SELECT id, name, parent_id, level
FROM department_cte
ORDER BY level, id;
这个查询首先使用 WITH RECURSIVE
语句定义了一个名为 department_cte
的递归 CTE。CTE 包含四个字段:id
、name
、parent_id
和 level
。level
字段表示部门在层次结构中的层级。
在 CTE 的定义中,我们首先从根部门(即 parent_id
为 NULL 的部门)开始,将其层级设置为 1。然后,我们使用 UNION ALL
与递归查询部分连接。递归查询部分通过将子部门与父部门(已在 CTE 中)连接在一起来查找子部门。在递归查询部分,我们将子部门的层级设置为父部门的层级加 1。
最后,我们从 CTE 中选择所有记录并按层级和 ID 对其进行排序。这将返回一个包含所有部门及其层次结构的结果集。