之前想简单了，需要多一层子查询，修改如下：

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;

参考我之前翻译整理的 https://segmentfault.com/a/11... 中《获取整棵树》一节的第二个例子，主要的修改是将(COUNT(parent.name) - 1) AS depth改成parent.id就可以得到类似邻接表的形态。