SQL练习笔记五：树节点

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;