如何选择层次结构中的记录的SQL查询？

假设有以下层次结构数据表：

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

INSERT INTO categories (id, name, parent_id) VALUES
  (1, 'Electronics', NULL),
  (2, 'Computers', 1),
  (3, 'Tablets', 2),
  (4, 'Phones', 1),
  (5, 'Laptops', 2),
  (6, 'Accessories', 1),
  (7, 'Cases', 6),
  (8, 'Chargers', 6),
  (9, 'Keyboards', 2);

现在要选择某个类别及其所有子孙类别。可以使用递归查询来解决此类问题。

下面是选择“Computers”类别及其所有子孙类别的查询：

WITH RECURSIVE category_recursion(id, name, parent_id) AS (
  SELECT id, name, parent_id
  FROM categories
  WHERE id = 2 -- desired category ID
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM categories c
  JOIN category_recursion cr ON cr.id = c.parent_id
)
SELECT * FROM category_recursion;

这将返回如下结果：

 id |     name      | parent_id
----|--------------|-----------
  2 | Computers    |         1
  3 | Tablets      |         2
  5 | Laptops      |         2
  9 | Keyboards    |         2

这里使用了WITH RECURSIVE语句来创建递归查询。首先选择给定的类别（在本例中为“Computers”），然后使用UNION ALL操作将该类别的所有子孙类别连接在一起。

请注意，这种方法不仅适用于类别层次结构，还适用于其他任何具有类似“父-子”关系的数据结构。