如何获取树结构中所有节点的子节点？ SQL查询？ [英] How to get all children of a node in tree structure ? SQL query?

问题描述

table - user

table - user

列 -
（userId，name，managerId）

columns - (userId ,name, managerId)

-

(1,nilesh,0)
(2,nikhil,1)    
(3,nitin ,2)  
(4,Ruchi,2)

它应该列出所有报告人给他。
如果我给userId = 2，它应该返回3,4。

if I give id of user it should list all reporting people to him . if I give userId = 2 it should return 3,4.

这个查询是否正确

SELECT ad3.userId
FROM user au , user  au2 , user  au3
WHERE 
    ad.managerId = ad2.managerId AND 
    ad3.managerId = ad2.userId AND
    ad.userId=2

管理DB中的树结构？
如何左右叶方式？

Is there any efficent way to manage tree structure in DB ? How about right and left leaf way ?

推荐答案

在我看来，邻接列表模型的问题是，它很难处理在SQL，不知道嵌套你的树结构的深度。

In my opinion, the problem with the adjacency list model is that it gets difficult to deal with in SQL especially when you don't know how deeply nested your tree structure is going to be.

你提到的左右叶方式可能是嵌套集合模型，允许你存储

The 'left and right leaf way' you mention is probably the nested set model and allows you to store things like this

LFT   RGT   Name
1     8      nilesh
2     7      nikhil
3     4      nitin
5     6      Ruchi

然后你可以找到所有的下属>

Then you can find all of anyones subordinates by simply

SELECT Name FROM Hierarchy WHERE LFT BETWEEN @LFT AND @RGT

我认为更容易处理查询，但是更难以做树修改。如果你的数据没有改变太多，我认为这是一个更好的解决方案。 （不是每个人都同意我）

I think it is much easier to deal with for querying but is harder to do for tree modifications. If your data doesn't change much then I think this is a much better solution. (Not everyone will agree with me though)

有一个很好的教程这里

这篇关于如何获取树结构中所有节点的子节点？ SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！