mysql - 查找mysql中用户的深度

Question

我有一个存储 user_id 和 parent_user_id 的表。例如:

user_id    parent_user_id
--------------------------
1          0
2          1
3          2
4          3
5          4

现在如果我想知道user_id 5的深度，(即5)我怎样才能得到它？我尝试了 SOF 中提到的很多查询，但没有一个能解决我的问题。我想要的只是查询通过遍历 5->4、4->3 .. 1->0 来告诉我深度。请帮助我。

更新:

我尝试过的一些查询

select user_id, @pv:=parent_id as 'parent_id' from user_referral
join
(select @pv:=7)tmp
where user_id=@pv;   

SELECT t1.user_id, t1.parent_id FROM
user_referral AS t1 LEFT JOIN user_referral as t2
ON t1.user_id = t2.parent_id
WHERE t2.user_id = 9 ORDER BY t2.referral_id;

但没有一个按预期工作。一件有趣的事情，没有人以递归方式给我答案，每个查询只给 mu 1 条记录!

我尝试过的一些链接:

Link1

Link2

Answer 1

一些 RDBMS 产品提供内置的分层查询。例如，Oracle SQL has一个START WITH ... CONNECT BY语法。

但不是 MySQL。你必须以某种方式伪造它。一种方法是检索数据并在应用程序的内存中构建层次结构。

另一种方法是使用 LEFT JOIN 序列比层次结构的预期深度长的操作。这个查询有点难看，但它有效。此类查询的每一行都包含该行的“谱系”。以下是您的数据示例 ( http://sqlfiddle.com/#!2/128372/4/0 ):

select a.user_name,
       COALESCE(g.user_id, f.user_id, e.user_id, d.user_id, c.user_id, b.user_id, a.user_id) root,
       (a.user_id IS NOT NULL) + (b.user_id IS NOT NULL) + (c.user_id IS NOT NULL) +
       (d.user_id IS NOT NULL) + (e.user_id IS NOT NULL) + (f.user_id IS NOT NULL) +
       (g.user_id IS NOT NULL) depth
  from hier a
  left join hier b on a.parent_user_id = b.user_id
  left join hier c on b.parent_user_id = c.user_id
  left join hier d on c.parent_user_id = d.user_id
  left join hier e on d.parent_user_id = e.user_id
  left join hier f on e.parent_user_id = f.user_id
  left join hier g on f.parent_user_id = g.user_id
  order by depth DESC

编码到 CONNECT BY parent_user_id = user_id 中的逻辑在 Oracle 分层查询中，此处显示为重复的 ON重复LEFT JOIN的条款操作。