SQL树结构 [英] SQL Tree Structure

问题描述

我对SQL中的这个主题(树结构)是陌生的.我经历了不同的来源，但仍不清楚.

I am new to this topic (i.e tree structure) in SQL. I have gone through different sources but still not clear.

在我的情况下，我有一张附有的桌子.

Here in my case, I have a table which I have attached herewith.

现在首先在这里，我必须为办公室"检索一棵完整树.

Now here first, I have to retrieve a Full Tree for "OFFICE".

我还必须在附加的层次结构数据中找到所有叶子节点(没有子节点的叶子节点).

Also i have to find all the leaf nodes (those with no Children) in the attached hierarchical data.

请提供答案并提供详细说明. 预先感谢.

Please provide the answers with detail explanation. Thanks in advance.

推荐答案

您未指定DBMS，而是使用标准SQL(所有

You didn't specify your DBMS but with standard SQL (supported by all modern DBMS) you can easily do a recursive query to get the full tree:

with recursive full_tree as (
  select id, name, parent, 1 as level
  from departments
  where parent is null
  union all 
  select c.id, c.name, c.parent, p.level + 1
  from departments c
    join full_tree p on c.parent = p.id
)
select *
from full_tree;

如果需要子树，只需在公共表表达式中更改开始条件.得到例如所有类别":

If you need a sub-tree, just change the starting condition in the common table expression. To get e.g. all "categories":

with recursive all_categories as (
  select id, name, parent, 1 as level
  from departments
  where id = 2 --- << start with a different node 
  union all 
  select c.id, c.name, c.parent, p.level + 1
  from departments c
    join all_categories p on c.parent = p.id
)
select *
from all_categories;

获取所有叶子很简单:在所有节点上，其ID都不显示为parent:

Getting all leafs is straightforward: it's all nodes where their ID does not appear as a parent:

select *
from departments
where id not in (select parent
                 from departments
                 where parent is not null);

SQLFiddle示例: http://sqlfiddle.com/#!15/414c9/1

SQLFiddle example: http://sqlfiddle.com/#!15/414c9/1

在指定DBMS之后进行编辑.

Edit after DBMS has been specified.

Oracle确实支持递归CTE(尽管您需要11.2.x)，您只需要省略关键字recursive.但是您也可以使用CONNECT BY运算符:

Oracle does support recursive CTEs (although you need 11.2.x for that) you just need to leave out the keyword recursive. But you can also use the CONNECT BY operator:

select id, name, parent, level
from departments
start with parent is null
connect by prior id = parent;

select id, name, parent, level
from departments
start with id = 2
connect by prior id = parent;

用于Oracle的SQLFiddle: http://sqlfiddle.com/#!4/6774ee/3

SQLFiddle for Oracle: http://sqlfiddle.com/#!4/6774ee/3

有关详细信息，请参见手册: https://docs.oracle. com/database/121/SQLRF/queries003.htm#i2053935

See the manual for details: https://docs.oracle.com/database/121/SQLRF/queries003.htm#i2053935

这篇关于SQL树结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！