SQL练习笔记五：树节点-技术圈

点击上方蓝字关注「网优小兵玩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 TypeFROM  treeWHERE  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;