SQL CASE语句具体例子

Question

我有一个查询，用于识别具有父关系的树中的节点。这个查询有什么问题？即使我有内部节点，相应的 case 语句也永远不会执行。我总是将结果作为叶节点或根节点。永远不要在输出中得到 Inner 。我可能做错了什么？

WITH CTE(N, P, [Level])
AS
(
    SELECT N, P, 1 FROM
    BST B
    WHERE P IS NULL
    UNION ALL
    SELECT B.N, B.P, [Level] + 1 FROM
    BST B 
    JOIN CTE ON B.P = CTE.N
)
SELECT N,
CASE
WHEN [Level] = 1 Then 'Root'
WHEN [Level] < MAX([Level]) AND [Level] > 1 THEN 'Inner'
WHEN [Level] = MAX([Level]) THEN 'Leaf'
END
FROM CTE
GROUP BY N, [Level]
ORDER BY N ASC;

示例输入

N P
1 2
3 2
6 8
9 8
2 5
8 5
5 NULL

示例输出

1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf

Answer 1

为什么要建hier？请注意，如果这是 Jagged Hierarchy，则 Max(Level) 不一定有效。例如，叶子节点是 3 级，但最大级别是 6。

Declare @YourTable table (N int,P int)
Insert Into @YourTable values
(1, 2),
(3, 2),
(6, 8),
(9, 8),
(2, 5),
(8, 5),
(5, NULL)

Select A.*
      ,Lvl = case when A.P is null then 'Root' else case when B.P is null then 'Leaf' else 'Inner' end end
 From  @YourTable A
 Left Join  (Select Distinct P from @YourTable) B on A.N=B.P

返回

N   P   Lvl
1   2   Leaf
3   2   Leaf
6   8   Leaf
9   8   Leaf
2   5   Inner
8   5   Inner
5   NULL    Root