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

问题描述

表 - 用户

列 -(userId,name, managerId)

columns - (userId ,name, managerId)

行 -

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

如果我给出用户的 id，它应该列出所有向他报告的人.如果我给 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屋！