跳到主要内容

SQL 层次查询

在SQL中,层次查询(Hierarchical Query)是一种用于处理树形结构数据的强大工具。树形结构数据通常表现为父子关系,例如组织结构、文件系统或分类目录。层次查询允许我们以递归的方式遍历这些结构,从而提取出所需的信息。

什么是层次查询?

层次查询是一种递归查询,用于处理具有层次结构的数据。它通过递归地遍历父子关系,生成一个层次化的结果集。常见的应用场景包括:

  • 组织结构:查询某个员工的所有下属。
  • 文件系统:查询某个目录下的所有子目录和文件。
  • 分类目录:查询某个分类的所有子分类。

基本语法

在SQL中,层次查询通常使用 WITH RECURSIVECONNECT BY 语法来实现。不同的数据库管理系统(DBMS)可能支持不同的语法。下面我们将分别介绍这两种语法。

使用 WITH RECURSIVE

WITH RECURSIVE 是SQL标准中用于递归查询的语法,适用于大多数现代数据库系统,如PostgreSQL、MySQL和SQLite。

sql
WITH RECURSIVE hierarchy AS (
-- 基础查询:选择根节点
SELECT id, name, parent_id
FROM employees
WHERE parent_id IS NULL

UNION ALL

-- 递归查询:选择子节点
SELECT e.id, e.name, e.parent_id
FROM employees e
INNER JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

在这个例子中,我们首先选择根节点(parent_id IS NULL),然后递归地选择每个节点的子节点,直到没有更多的子节点为止。

使用 CONNECT BY

CONNECT BY 是Oracle数据库中用于层次查询的语法。它使用 START WITHCONNECT BY PRIOR 来定义递归关系。

sql
SELECT id, name, parent_id
FROM employees
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

在这个例子中,START WITH 定义了根节点,CONNECT BY PRIOR 定义了父子关系。

实际案例

假设我们有一个员工表 employees,其结构如下:

sql
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
parent_id INT
);

表中数据如下:

sql
INSERT INTO employees (id, name, parent_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

查询所有下属

假设我们想要查询Alice的所有下属,可以使用以下查询:

sql
WITH RECURSIVE hierarchy AS (
SELECT id, name, parent_id
FROM employees
WHERE name = 'Alice'

UNION ALL

SELECT e.id, e.name, e.parent_id
FROM employees e
INNER JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

输出结果如下:

idnameparent_id
1AliceNULL
2Bob1
3Charlie1
4David2
5Eve2
6Frank3

查询层级深度

我们还可以查询每个员工的层级深度:

sql
WITH RECURSIVE hierarchy AS (
SELECT id, name, parent_id, 1 AS level
FROM employees
WHERE name = 'Alice'

UNION ALL

SELECT e.id, e.name, e.parent_id, h.level + 1
FROM employees e
INNER JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

输出结果如下:

idnameparent_idlevel
1AliceNULL1
2Bob12
3Charlie12
4David23
5Eve23
6Frank33

总结

层次查询是处理树形结构数据的强大工具,它允许我们以递归的方式遍历父子关系。通过 WITH RECURSIVECONNECT BY 语法,我们可以轻松地查询出层次结构中的所有节点及其层级深度。

附加资源

练习

  1. 修改上述查询,使其返回每个员工的完整路径(例如:Alice/Bob/David)。
  2. 尝试在MySQL或PostgreSQL中实现一个层次查询,查询某个分类的所有子分类。
提示

在实际应用中,层次查询可能会涉及大量数据,因此请确保在递归查询中设置适当的终止条件,以避免无限递归。