mysql 树排序_mysql按树深度排序

我有表'pe'与列id，名称和lcltyid我有表'wp_exrz_locality_localities'与id，name和parent

locality表是树，父级包含另一个位置行的id . pe.lcltyid是关键字wp_exrz_locality_localities.id

基本上我想做的是检索按“树深度”排序的所有'pe'条目

然而，树的总深度可以是任何时间的任何量 . 我需要深度的方式允许我在子查询中使用它进行排序 .

最初我以为我需要一个存储的进程/函数来获取lclty条目的深度 . 在我完成这个过程后，我发现表达式中不能使用 . 然后我尝试创建一个函数，但是我的主机和“log_bin_trust_function_creators = 0”启用了二进制日志记录，因此我没有存储函数 .

最后，我试图理解递归，但似乎无法使它工作 . 我只是想创建一个递归语句来检索“深度”，这意味着直到顶级节点的单个节点的父节点数，或者当父节点= 0时我只是得到一个错误“在'RECURSIVE node_ancestors附近使用的语法......”

WITH RECURSIVE node_ancestors(id, parent) AS (

SELECT id, id FROM `wp_exrz_locality_localities` WHERE id IN (1, 2, 3)

UNION ALL

SELECT na.id, wp_exrz_locality_localities.parent

FROM node_ancestors AS na, wp_exrz_locality_localities

WHERE wp_exrz_locality_localities.id = na.parent AND wp_exrz_locality_localities.parent != 0

)

SELECT id, COUNT(parent) AS depth FROM node_ancestors GROUP BY id;

任何帮助是极大的赞赏

一个例子： EDIT 表pe：

id---name---lcltyid

2---first---4

3---second---3

表wp_exrz_locality_localities：

id---name---parent

1---USA---0

3---SanFran---1

4---California---3

SELECT * FROM 'pe' ORDER BY ([lcltydepth]) ASC;

期望的输出：

id---name---lcltyid

3---second---3

2---first---4

其中lclctydepth为3表示“第一”pe，2表示“秒”，因为第二个附加到一个只有美国高于它的状态，第一个附加到一个城市，州和美国高于它 . 所以它会按照父母的数量来命令他们获得parentid = 0的最后一个父母;

我希望这有帮助？