У меня есть таблица в базе данных MySQL, а именно "пользователи"
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 — ЕСЛИ родительский идентификатор равен NULL:
Дело №2: - ЕСЛИ Parent Id равен 1 (что означает, что пользователь был создан самым первым предком)
Дело №3: - ЕСЛИ родительский идентификатор равен N (что означает, что пользователь был создан N-м дочерним элементом случая № 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. Неизвестный столбец «USR.user_id» в «предложении where»
Пожалуйста, помогите мне, как передать значение внутри CTE, где условие из приведенного выше запроса и как добиться запрошенного вывода.
Я не думаю, что это лучший подход, так как это будет стоить вам больших затрат на обработку и может стать кошмаром для дальнейшего обслуживания вашего приложения. Вы должны просто сохранить пользователя root для каждого пользователя в базе данных (это то, как большинство древовидных систем справляются с этим). Ваша структура таблицы станет: идентификатор_пользователя | имя | parent_user_id | root_user_id
Если вы действительно хотите продолжать в том же духе, я бы посоветовал вам проверить это (вам просто нужно добавить к нему условие «не пользователь 1 корень»): https://dba.stackexchange.com/questions/7147/найти-наивысший-уровень-из-иерархического-поля-с-vs-без-ctes/7150