SQL 树查询

最新推荐文章于 2025-05-06 17:51:14 发布

彖爻之辞

最新推荐文章于 2025-05-06 17:51:14 发布

阅读量5.5k

点赞数 1

分类专栏： --------1.2 SQL SERVER 文章标签： sql

本文链接：https://blog.csdn.net/paul50060049/article/details/76400675

版权

--------1.2 SQL SERVER 专栏收录该内容

20 篇文章

订阅专栏

本文介绍了树数据结构的基本概念，如节点的度、父子节点、兄弟节点的关系，并探讨了如何使用SQL来定义和操作树形数据，对于理解和处理树型数据库具有指导意义。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、树的定义
树是一种包括祖父节点、父节点、子节点、孙节点的一种数据结构。通常定义为具有n个节点的有限集合。
度是树的节点的一种分类，
节点的度：一个节点拥有的子节点即成为节点的度，比如A节点，有B和C两个子节点，那么A节点的度=2。
节点间关系
子节点：某一个节点的子节点称为孩子节点。比如B、C节点是A节点的孩子节点。
父节点：与孩子节点相反。比如，A节点是B、C的父节点。
兄弟节点：同一个双亲节点的孩子节点，之间称为兄弟节点。比如，B、C为兄弟节点。

二、树的表定义
一个树的数据类型，一般用表可以定义出来，通常定义的表可以包括下面的内容：

  CREATE TABLE tb_Tree
  (
    fPID       VARCHAR2(6),          -- 节点ID      Position ID
    fFPID      VARCHAR2(6),          -- 父节点ID    Father Position ID
    fArea      VARCHAR2(1),          -- 区域        Father Area
    fLayer     Number,               -- 层
    CONSTRAINT pk_Tree PRIMARY KEY (fPID)
  );
  Comment On Column tb_Tree.fPID        is '节点ID';
  Comment On Column tb_Tree.fFPID       is '父节点ID';
  Comment On Column tb_Tree.fLayer      is '层';
  Comment On Column tb_Tree.fMID        is '成员ID';
  Comment On Column tb_Tree.fRDate      is '注册时间';
  Comment On Column tb_Tree.fStatus     is '节点状态';
三、样本数据
  INSERT INTO tb_TREE VALUES ('E00000','ROOT',  '0',01);

  INSERT INTO tb_TREE VALUES ('E00001','E00000','A',02);
  INSERT INTO tb_TREE VALUES ('E00002','E00000','B',02);

  INSERT INTO tb_TREE VALUES ('E00003','E00001','A',03);
  INSERT INTO tb_TREE VALUES ('E00004','E00001','B',03);
  INSERT INTO tb_TREE VALUES ('E00005','E00002','A',03);
  INSERT INTO tb_TREE VALUES ('E00006','E00002','B',03);

  INSERT INTO tb_TREE VALUES ('E00007','E00003','A',04);
  INSERT INTO tb_TREE VALUES ('E00008','E00003','B',04);
  INSERT INTO tb_TREE VALUES ('E00009','E00004','A',04);
  INSERT INTO tb_TREE VALUES ('E00010','E00004','B',04);
  INSERT INTO tb_TREE VALUES ('E00011','E00005','A',04);
  INSERT INTO tb_TREE VALUES ('E00012','E00005','B',04);
  INSERT INTO tb_TREE VALUES ('E00013','E00006','A',04);
  INSERT INTO tb_TREE VALUES ('E00014','E00006','B',04);

  INSERT INTO tb_TREE VALUES ('E00015','E00007','A',05);
  INSERT INTO tb_TREE VALUES ('E00016','E00007','B',05);
  INSERT INTO tb_TREE VALUES ('E00017','E00008','A',05);
  INSERT INTO tb_TREE VALUES ('E00018','E00008','B',05);
  INSERT INTO tb_TREE VALUES ('E00019','E00009','A',05);
  INSERT INTO tb_TREE VALUES ('E00020','E00009','B',05);
  INSERT INTO tb_TREE VALUES ('E00021','E00010','A',05);
  INSERT INTO tb_TREE VALUES ('E00022','E00010','B',05);
  INSERT INTO tb_TREE VALUES ('E00023','E00011','A',05);
  INSERT INTO tb_TREE VALUES ('E00024','E00011','B',05);
  INSERT INTO tb_TREE VALUES ('E00025','E00012','A',05);
  INSERT INTO tb_TREE VALUES ('E00026','E00012','B',05);
  INSERT INTO tb_TREE VALUES ('E00027','E00013','A',05);
  INSERT INTO tb_TREE VALUES ('E00028','E00013','B',05);
  INSERT INTO tb_TREE VALUES ('E00029','E00014','A',05);
  INSERT INTO tb_TREE VALUES ('E00030','E00014','B',05);

  INSERT INTO tb_TREE VALUES ('E00031','E00015','A',06);
  INSERT INTO tb_TREE VALUES ('E00032','E00015','B',06);
  INSERT INTO tb_TREE VALUES ('E00033','E00016','A',06);
  INSERT INTO tb_TREE VALUES ('E00034','E00016','B',06);
  INSERT INTO tb_TREE VALUES ('E00035','E00017','A',06);
  INSERT INTO tb_TREE VALUES ('E00036','E00017','B',06);
  INSERT INTO tb_TREE VALUES ('E00037','E00018','A',06);
  INSERT INTO tb_TREE VALUES ('E00038','E00018','B',06);
  INSERT INTO tb_TREE VALUES ('E00039','E00019','A',06);
  INSERT INTO tb_TREE VALUES ('E00040','E00019','B',06);
  INSERT INTO tb_TREE VALUES ('E00041','E00020','A',06);
  INSERT INTO tb_TREE VALUES ('E00042','E00020','B',06);
  INSERT INTO tb_TREE VALUES ('E00043','E00021','A',06);
  INSERT INTO tb_TREE VALUES ('E00044','E00021','B',06);
  INSERT INTO tb_TREE VALUES ('E00045','E00022','A',06);
  INSERT INTO tb_TREE VALUES ('E00046','E00022','B',06);
  INSERT INTO tb_TREE VALUES ('E00047','E00023','A',06);
  INSERT INTO tb_TREE VALUES ('E00048','E00023','B',06);
  INSERT INTO tb_TREE VALUES ('E00049','E00024','A',06);
  INSERT INTO tb_TREE VALUES ('E00050','E00024','B',06);
  INSERT INTO tb_TREE VALUES ('E00051','E00025','A',06);
  INSERT INTO tb_TREE VALUES ('E00052','E00025','B',06);
  INSERT INTO tb_TREE VALUES ('E00053','E00026','A',06);
  INSERT INTO tb_TREE VALUES ('E00054','E00026','B',06);
  INSERT INTO tb_TREE VALUES ('E00055','E00027','A',06);
  INSERT INTO tb_TREE VALUES ('E00056','E00027','B',06);
  INSERT INTO tb_TREE VALUES ('E00057','E00028','A',06);
  INSERT INTO tb_TREE VALUES ('E00058','E00028','B',06);
  INSERT INTO tb_TREE VALUES ('E00059','E00029','A',06);
  INSERT INTO tb_TREE VALUES ('E00060','E00029','B',06);
  INSERT INTO tb_TREE VALUES ('E00061','E00030','A',06);
  INSERT INTO tb_TREE VALUES ('E00062','E00030','B',06);

四、根节点的特殊性 
一个树，定义其根节点的方法有多种，但是必须有一个特殊的含义，在这里，根节点设计了fPID = ‘E00000’。我们定义这个树只有一个根节点。 
查询根节点：

  SELECT * FROM tb_Tree WHERE fFPID = 'ROOT';
  SELECT * FROM tb_Tree WHERE fArea = '0';
  SELECT * FROM tb_Tree WHERE fPID  = 'E00000';3. 查找一个节点的所有直属子节点（所有后代）。

五、查询所有父节点、层和区域
  1. 查找一个节点的所有直属子节点（所有后代）。
        WITH CTE ( fPID, fFPID, lev) AS 
        (
                SELECT fPID, fFPID, 1 AS lev FROM tb_Tree WHERE fPID = 'E00011'
                UNION ALL
                SELECT E.fPID, E.fFPID, lev + 1 FROM CTE T, tb_Tree E
                WHERE T.fPID = E.fFPID
        )
        SELECT * FROM CTE;

    查询结果如下：
    fPID    fFPID   lev
        --------------------
        E00011  E00005  1
        E00023  E00011  2
        E00024  E00011  2
        E00049  E00024  3
        E00050  E00024  3
        E00047  E00023  3
        E00048  E00023  3