HackerRank二叉树节点分类SQL查询未通过求助及解法技巧

输入说明 #

表格包含两列：N（节点值）、P（父节点值），示例输入数据如下：

输出说明 #

需要为每个节点分类，输出节点值和对应的类型（root/inner/leaf），示例输出如下：

我的第一次尝试代码 #

with tb1 as
(
   select N,P from binary_tree
), tb2 as 
(
   select N,P from binary_tree
)
select t2.N as t2_N,
case 
     when t2.P is null and t1.P is null  and t1.N is null then 'root'
     when t2.P is not null and t1.P is null and t1.N is not null then 'inner'
     ELSE 'leaf'
end as RESULT
from tb2 t2 LEFT JOIN tb1 t1 ON t2.P = t1.N order by t2.N;

我的第二次尝试代码 #

with tb1 as
(
   select N,P from BST
), tb2 as 
(
   select P from BST
)
select distinct t.* from (select t1.N as tn,
case 
    when t1.N is not null and t2.P is not null and t1.P is null then 'root'
    when t1.N is not null and t2.P is not null and t1.P is not null then 'inner'
    when t1.N is not null and t2.P is null and t1.P is not null then 'leaf'
end as RESULT
from tb1 t1 LEFT JOIN tb2 t2 on t1.N = t2.P) t order by tn;

问题 #

我写的这两个查询能得到预期输出，但提交后未被HackerRank平台接受，希望帮忙排查原因，并讲解此类二叉树问题的解决方法及技巧。

内容的提问来源于stack exchange，提问作者yyy62103