SQL练习笔记五：树节点

用户6184845

发布于 2021-01-04 11:31:36

3840

发布于 2021-01-04 11:31:36

文章被收录于专栏：网优小兵玩Python

每个节点都是以下三种类型中的一种：

Root: 如果节点是根节点。
Leaf: 如果节点是叶子节点。
Inner: 如果节点既不是根节点也不是叶子节点。

对于tree表，id是树节点的标识，p_id是其父节点的id。

CREATE TABLE tree(
  id int(4) not null,
  p_id int(4)
);

insert INTO tree (id) VALUES(1);
insert INTO tree VALUES(2,1);
insert INTO tree VALUES(3,1);
insert INTO tree VALUES(4,2);
insert INTO tree VALUES(5,2);

-- 根节点查询 --
SELECT
  id,
  @Type := 'Root' AS Type 
FROM
  tree 
WHERE
  p_id IS NULL;

-- 内部节点查询 --
SELECT
  id,
  @Type := 'Inner' AS Type 
FROM
  tree 
WHERE
  id IN ( SELECT DISTINCT p_id FROM tree WHERE p_id IS NOT NULL ) 
  AND p_id IN ( SELECT DISTINCT id FROM tree WHERE id IS NOT NULL );

-- 叶子节点查询 --
SELECT
  id,
  @Type := 'Leaf' AS Type
FROM
  tree
WHERE
  id not in(SELECT DISTINCT p_id FROM tree WHERE p_id is not null);

-- 解法：case条件分支判断解决
SELECT
  id,
  (
  CASE
      WHEN p_id IS NULL THEN 'Root' 
      WHEN id NOT IN ( SELECT DISTINCT p_id FROM tree WHERE p_id IS NOT NULL ) THEN  'Leaf'
      ELSE 'Inner' 
    END 
  ) AS Type 
FROM
  tree;