I thought it was simple before and needed one more layer of subqueries. The modification is as follows:
SELECT node.name, parent.id AS pid
FROM `nested` as node
LEFT OUTER JOIN `nested` AS parent
ON parent.`left` = (
SELECT MAX(parents.`left`)
FROM nested AS parents
WHERE node.`left` > parents.`left` AND node.`left` < parents.`right`
)
ORDER BY node.id;
Refer to the second example in the "Getting the Whole Tree" section of https://segmentfault.com/a/11... that I translated and compiled before. The main modification is to change (COUNT(parent.name) - 1) AS depth 改成 parent.id to get a form similar to the adjacency list. .
I thought it was simple before and needed one more layer of subqueries. The modification is as follows:
Refer to the second example in the "Getting the Whole Tree" section of https://segmentfault.com/a/11... that I translated and compiled before. The main modification is to change
(COUNT(parent.name) - 1) AS depth
改成parent.id
to get a form similar to the adjacency list. .