CTE 错误：“锚点和递归部分之间的类型不匹配”

CTE 错误：“锚点和递归部分之间的类型不匹配”

在编写复杂的 SQL 查询语句时，我们经常会使用到递归查询（Recursive Query），这是一种非常强大且有用的技术。然而，在使用递归查询时，有时候我们会遇到一个常见的错误：“锚点和递归部分之间的类型不匹配”（CTE error: types of anchor and recursive part are not compatible）。

这个错误通常出现在递归查询的锚点和递归部分之间的数据类型不匹配时。在递归查询中，我们需要定义一个锚点（Anchor）和一个递归部分（Recursive part），并且它们的数据类型必须一致。

为了更好地理解这个错误，让我们来看一个案例。

案例

假设我们有一个名为“employees”的表，其中包含员工的信息，如员工ID、姓名和直接上级的员工ID。我们想要使用递归查询来查找每个员工的所有下属。

首先，让我们创建一个示例表：

sql
CREATE TABLE employees (
  employee_id INT,
  name VARCHAR(50),
  manager_id INT
);
INSERT INTO employees (employee_id, name, manager_id)
VALUES (1, 'John', NULL),
       (2, 'Jane', 1),
       (3, 'Mike', 1),
       (4, 'Sarah', 2),
       (5, 'David', 3),
       (6, 'Emily', 3);

现在，我们尝试执行以下递归查询：

sql
WITH RECURSIVE employees_hierarchy AS (
  -- 锚点部分
  SELECT employee_id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归部分
  SELECT e.employee_id, e.name, e.manager_id
  FROM employees e
  JOIN employees_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employees_hierarchy;

在这个查询中，我们首先定义了一个锚点部分，它选择了没有上级的员工。然后，我们使用 UNION ALL 运算符将递归部分与锚点部分连接起来。递归部分通过将员工表（employees）与递归查询（employees_hierarchy）进行连接来查找每个员工的下属。

然而，如果我们运行这个查询，就会遇到我们之前提到的错误：“锚点和递归部分之间的类型不匹配”。这是因为我们在锚点部分和递归部分中选择的列不匹配。

解决方案

要解决这个错误，我们需要确保锚点部分和递归部分选择的列具有相同的数据类型。在我们的案例中，我们需要在锚点部分和递归部分中选择相同的列。

让我们修改递归查询的代码，使其选择相同的列：

sql
WITH RECURSIVE employees_hierarchy AS (
  -- 锚点部分
  SELECT employee_id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归部分
  SELECT e.employee_id, e.name, e.manager_id
  FROM employees e
  JOIN employees_hierarchy eh ON e.employee_id = eh.manager_id
)
SELECT *
FROM employees_hierarchy;

在这个修改后的查询中，我们在锚点部分和递归部分都选择了相同的列：employee_id、name 和 manager_id。现在，如果我们再次运行查询，就不会再遇到“锚点和递归部分之间的类型不匹配”的错误了。

在编写递归查询时，确保锚点部分和递归部分选择相同的列是非常重要的。如果选择的列不匹配，就会导致“锚点和递归部分之间的类型不匹配”的错误。通过修改查询，使锚点部分和递归部分选择相同的列，我们可以解决这个错误并成功执行递归查询。