如何获取一棵树的所有叶子？

Question

create table t_tree(id,superid);
ID SUPERID
1001 0
100110 1001
100111 1001
10011101 100111
10011102 100111
10011103 100111
10011104 100111
10011105 100111
100101 1001
10010101 100101
10010102 100101
100102 1001
100103 1001
10010301 100103
10010302 100103
100104 1001
100105 1001
100106 1001
100109 1001
10010901 100109
10010902 100109
10010903 100109
10010904 100109
100112 1001
1002 0
100201 1002
100202 1002
想要获取根节点下的所有叶子（指最末端）；目标结构：
ID SUPERID
100110 1001
10011101 100111
10011102 100111
10011103 100111
10011104 100111
10011105 100111
10010101 100101
10010102 100101
100102 1001
10010301 100103
10010302 100103
100104 1001
100105 1001
100106 1001
10010901 100109
10010902 100109
10010903 100109
10010904 100109
100112 1001
100201 1002
100202 1002

Answer 1

我给你几个SQL SERVER的例.-- 树形数据层次显示处理示例
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

Answer 2

--树形数据广度排序处理示例.
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--广度排序显示处理
--生成每个节点的层次数据
DECLARE @t_Level TABLE(ID char(3),Level int)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Level,b.ID
/*--结果
ID   PID  Name       
------- --------- ---------- 
001  NULL 山东省
005  NULL 四会市
002  001   烟台市
003  001   青岛市
006  005   清远市
004  002   招远市
007  006   小分市
--*/

Answer 3

-- 树形数据深度排序处理示例(递归法)
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT  --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID   PID   Name       
------ --------- ---------- 
001  NULL 山东省
002  001   烟台市
004  002   招远市
003  001   青岛市
005  NULL 四会市
006  005   清远市
007  006   小分市
--*/

Answer 4

--树形数据深度排序处理示例(模拟单编号法)
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID   PID   Name       
------ --------- ---------- 
001  NULL 山东省
002  001   烟台市
004  002   招远市
003  001   青岛市
005  NULL 四会市
006  005   清远市
007  006   小分市
--*/

Answer 5

dawugui，谢谢你，有否start with connect by 的做法？

Answer 6

用个偷懒的方法：select * from t_tree where id not in(
select t1.id from t_tree t1,t_tree t2 where t1.id=substr(t2.id,0,length(t1.id))
and length(t1.id)!=length(t2.id));ID                   SUPERID
-------------------- --------------------
100110               1001
10011101             100111
10011102             100111
10011103             100111
10011104             100111
10011105             100111
10010101             100101
10010102             100101
100102               1001
10010301             100103
10010302             100103ID                   SUPERID
-------------------- --------------------
100104               1001
100105               1001
100106               1001
10010901             100109
10010902             100109
10010903             100109
10010904             100109
100112               1001
100201               1002
100202               1002已选择21行。

Answer 7

Oracle提供了“connect by … start with”，专门用来处理树级数据。
以下SQL可以返回所有子节点，以及子节点的层级。select 
  t.id,
  level,
  sys_connect_by_path(t.id,';') 
from t_tree t 
connect by prior t.id = t.superid
start with t.superid='0'

Answer 8

Oracle提供了“connect by … start with”，专门用来处理树级数据。
以下SQL可以返回所有子节点，以及子节点的层级。select 
  t.id,
  level,
  sys_connect_by_path(t.id,';') 
from t_tree t 
connect by prior t.id = t.superid
start with t.superid='0'

Answer 9

查看一下Connect by的用法就知道了，很简单，一句话！

Answer 10

是啊   oracle中就好做一点    

Answer 11

楼上的sulins，kivenchen24；
oracle的“connect   by   …   start   with”我是知道的，但我的问题是获取所有叶子（不包括分支节点），而不是整棵树；

Answer 12

该问题已经想到解决办法，采用叶子标示的方式来处理：
select * from  t_tree where flag='Y' start with id ='1201' connect by prior id =SUPER_ID

Answer 13

采用叶子标示的方式来处理：
select   *   from     t_tree   where   flag='Y'   start   with   id   ='1201'   connect   by   prior   id   =SUPER_ID