SQL 如何在树结构中获取节点的所有子节点？SQL查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6274942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get all children of a node in tree structure ? SQL query?
提问by Neel Salpe
table - user
表 - 用户
columns - (userId ,name, managerId)
列 - (userId ,name, managerId)
rows -
行 -
(1,nilesh,0)
(2,nikhil,1)
(3,nitin ,2)
(4,Ruchi,2)
if I give id of user it should list all reporting people to him . if I give userId = 2 it should return 3,4.
如果我给用户 id，它应该列出所有向他报告的人。如果我给 userId = 2 它应该返回 3,4。
Is this query correct
这个查询是否正确
SELECT ad3.userId
FROM user au , user au2 , user au3
WHERE
ad.managerId = ad2.managerId AND
ad3.managerId = ad2.userId AND
ad.userId=2
Is there any efficent way to manage tree structure in DB ? How about right and left leaf way ?
有没有有效的方法来管理 DB 中的树结构？左右叶方式怎么样？
回答by jgauffin
I use a text field to deal with trees in SQL. It's easier than using left/right values.
我使用文本字段来处理 SQL 中的树。这比使用左/右值更容易。
Lets take the example from the MySQL article:
让我们以 MySQL 文章中的示例为例：
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| GAME CONSOLES |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
It would result in a table like this:
它会产生一个像这样的表：
Id ParentId Lineage Name
1 null /1/ ELECTRONICS
2 1 /1/2/ TELEVISIONS
3 2 /1/2/3/ TUBE
4 2 /1/2/4/ LCD
5 2 /1/2/5/ PLASMA
6 6 /1/6/ GAME CONSOLES
7 1 /1/7/ PORTABLE ELECTRONICS
8 7 /1/7/8/ MP3 PLAYERS
9 8 /1/7/8/9/ FLASH
10 7 /1/7/10/ CD PLAYERS
11 1 /1/11/ 2 WAY RADIOS
12 11 /1/11/12/ FRS
Do find all portables you simply use the Lineage from portables:
请找到所有便携式设备，您只需使用便携式设备中的 Lineage：
SELECT * FROM theTable WHERE Lineage LIKE '/1/7/%'
Cons:
缺点：
- You need to do a UPDATE after each INSERT to append PK to Lineage
- 您需要在每次 INSERT 后执行 UPDATE 以将 PK 附加到 Lineage
Suggestion:
建议：
I usally add another column where I put the path as text in (for instance 'electronics/televisions/tube')
我通常会添加另一列，将路径作为文本放入（例如'electronics/televisions/tube'）
回答by a_horse_with_no_name
Something like this (ANSI SQL):
像这样（ANSI SQL）：
WITH RECURSIVE emptree (userid, name, managerid) AS (
SELECT userid,
name,
managerid
FROM the_table
WHERE userid = 2
UNION ALL
SELECT c.userid,
c.name,
c.managerid
FROM the_table c
JOIN emptree p ON p.userid = c.managerid
)
SELECT *
FROM emptree
回答by David Steele
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.
在我看来，邻接表模型的问题在于它很难在 SQL 中处理，尤其是当您不知道树结构的嵌套深度时。
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)
我认为处理查询要容易得多，但对于树修改则更难。如果您的数据变化不大，那么我认为这是一个更好的解决方案。（虽然不是每个人都会同意我的看法）
There is a Very good Tutorial here
这里有一个非常好的教程
回答by Dmitri Gudkov
SELECT user.id FROM user WHERE user.managerid = 2
Is this what you want?
这是你想要的吗？
