mysql - 将值从 Select 传递到 MySQL 中的 CROSS JOIN CTE

Question

我在 MySQL 数据库中有一个表，即“users”

CREATE TABLE `users` (
  `user_id` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `parent_user_id` int
);

INSERT INTO `users` (`user_id`, `name`, `parent_user_id`) VALUES
(1, 'John', null), 
(2, 'Emma', 1), 
(3, 'Watson', 2),
(4, 'Peter', 3), 
(5, 'Rose', 1), 
(6, 'Harry', 4),
(7, 'Jim', 6), 
(8, 'Jack', 5), 
(9, 'Josh', 8),
(10, 'Jem', 9);

我需要查询最顶层的表users而不是root用户(即root用户是1)

预期输出:

user_id     name          root_parent_user_id     root_parent_user_name
______________________________________________________________
1           John          null               null
2           Emma          1                  John
3           Watson        2                  Emma
4           Peter         2                  Emma
5           Rose          1                  John
6           Harry         2                  Emma
7           Jim           2                  Emma
8           Jack          5                  Rose
9           Josh          5                  Rose
10          Jem           5                  Rose

需求逻辑:

情况 #1 - 如果父 ID 为 NULL:

• 用户的parent_id为空，所以这是第一个用户，它没有任何父/祖先

情况 #2: - IF Parent Id 为 1(这意味着用户是由第一个祖先创建的)

• 用户的parent_id为1，所以我们需要取parent 1

案例 #3: - IF 父 ID 为 N(这意味着用户是由案例 #2 的第 N 个子级创建的)

• 用户的parent_id为n(例如:user_id为6，n为4)，因此我们需要找到根父级，它是由第一个祖先创建的，最后答案是“user_id:2”(即，艾玛)。

我尝试了以下代码:

SELECT * FROM users USR
CROSS JOIN (
WITH RECURSIVE cte (user_id, name, parent_user_id) AS
    (
      SELECT user_id, name, parent_user_id
        FROM users
        WHERE user_id = USR.user_id
      UNION ALL
      SELECT c.user_id, c.name, c.parent_user_id
        FROM cte AS cp JOIN users AS c
          ON cp.parent_user_id = c.user_id
    )
    SELECT * FROM cte where parent_user_id = 1
) PUSR ON 1 = 1;

我遇到错误错误代码:1054。“where 子句”中的未知列“USR.user_id”

请帮助我如何在上述查询的 CTE where 条件中传递值以及如何实现请求的输出。

Answer 1

我认为这不是最好的方法，因为它会在处理过程中花费大量成本，并且可能会成为以后维护应用程序的噩梦。您应该简单地将每个用户的根用户存储在数据库中(这是大多数树系统处理此问题的方式)。你的表结构将变成:用户 ID |名称 |父用户 ID | root_user_id

如果你真的想坚持这种方式，我建议你检查一下(你只需添加“非用户 1 root”条件即可): https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7150