打印sql中每个输出行旁边的字符串？

I have been looking for an answer to this question. I have a table called "Tree" that contains pid of the node and another column that contains parent pid (ppid). And the table contains:

我一直在寻找这个问题的答案。我有一个名为“Tree”的表,其中包含节点的pid和另一个包含父pid(ppid)的列。该表包含:

Pid     Ppid
1           2
3           2
5           6
7           6
2           4
6           4
4           15
8           9
10      9
12      13
14      13
9           11
13      11
11      15
15      NULL

The aim is to print a message next to the Pid column stating the type of node, ie "Leaf", "Inner" or "Root", in a single query using Oracle/sql/sql server/etc. The output is supposed to be something like this:

目的是在使用Oracle / sql / sql server / etc的单个查询中打印Pid列旁边的消息,说明节点的类型,即“Leaf”,“Inner”或“Root”。输出应该是这样的:

1           Leaf
2           Inner
3           Leaf
4           Inner
5           Leaf
6           Inner
7           Leaf
8           Leaf
9           Inner
10      Leaf
11      Inner
12      Leaf
13      Inner
14      Leaf
15      Root

I just need to know the way of printing the new column values. Can someone please help me? Thank you in advance :)

我只需要知道打印新列值的方法。有人可以帮帮我吗?先感谢您 :)

1 个解决方案

#1

In SQL SERVER & Oracle it can achieved using Recursive CTE

在SQL SERVER和Oracle中,它可以使用递归CTE实现

This is for SQL SERVER

这适用于SQL SERVER

;WITH data
     AS (SELECT *
         FROM   (VALUES (1,2 ),
                        (3,2 ),
                        (5,6 ),
                        (7,6 ),
                        (2,4 ),
                        (6,4 ),
                        (4,15 ),
                        (8,9 ),
                        (10,9 ),
                        (12,13 ),
                        (14,13 ),
                        (9,11 ),
                        (13,11 ),
                        (11,15 ),
                        (15,NULL ) ) tc (Pid, Ppid)),
     rec_cte
     AS (SELECT *
         FROM   data
         UNION ALL
         SELECT d.*
         FROM   rec_cte rc
                JOIN data d
                  ON rc.Ppid = d.Pid)
--select * from rec_cte
    SELECT Pid,
           CASE Count(Ppid)
             WHEN 0 THEN 'root'
             WHEN 1 THEN 'leaf'
             ELSE 'inner'
           END level
    FROM   rec_cte
    GROUP  BY Pid

Result :

╔═════╦═══════╗
║ Pid ║ level ║
╠═════╬═══════╣
║   1 ║ leaf  ║
║   2 ║ inner ║
║   3 ║ leaf  ║
║   4 ║ inner ║
║   5 ║ leaf  ║
║   6 ║ inner ║
║   7 ║ leaf  ║
║   8 ║ leaf  ║
║   9 ║ inner ║
║  10 ║ leaf  ║
║  11 ║ inner ║
║  12 ║ leaf  ║
║  13 ║ inner ║
║  14 ║ leaf  ║
║  15 ║ root  ║
╚═════╩═══════╝

#1