SQL练习笔记五：树节点

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

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

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

代码语言：javascript复制

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);

代码语言：javascript复制

-- 根节点查询 --
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);

代码语言：javascript复制

-- 解法：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;

网优苦短，我用Python

python tree

0 人点赞