研究了CrazyFor（蚂蚁）的树型结构问题，再提出2个问题

Question

找出页节点
select  a.TC_Name
from TreeClass a
where not exists
(select * from TreeClass b where b.TC_PID=a.TC_ID)--加拿大
北京
上海
常熟
南京
无锡
纽约
旧金山

Answer 1

层次排列 BOOKS ONLINE中有类似的例子（飞行路线）
select a.TC_Name as TC_PID ,s.TC_Name as TC_ID into #tmp1
from TreeClass a inner join TreeClass s on a.TC_id=s.TC_PID
where a.TC_Name<>S.TC_Name得到TC_PID  TC_ID
中国 北京
中国 上海
中国 江苏
江苏 苏州
苏州 常熟
江苏 南京
江苏 无锡
美国 纽约
美国 旧金山
CREATE PROCEDURE expand (@current varchar(50)) as
DECLARE @level int, @line varchar(50)
CREATE TABLE #stack (item varchar(50), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1WHILE @level > 0
BEGIN
   IF EXISTS (SELECT * FROM #stack WHERE level = @level)
      BEGIN
         SELECT @current = item
         FROM #stack
         WHERE level = @level
        -- select  distinct @TC_Name=TC_Name from #tmp1 where TC_PID=@current
        -- SELECT @line = replicate(char(3),@level) + @TC_Name 
        SELECT @line = space(@level - 1) + @current
         PRINT @line
         DELETE FROM #stack
         WHERE level = @level
            AND item = @current
         INSERT #stack
            SELECT TC_ID, @level + 1
            FROM #tmp1
            WHERE TC_PID = @current
         IF @@ROWCOUNT > 0
            SELECT @level = @level + 1
      END
   ELSE
      SELECT @level = @level - 1
END example:expand '中国'中国
 江苏
  无锡
  南京
  苏州
   常熟
 上海
 北京

Answer 2

another methodcreate procedure Get (@TC_Name char(50))
as
create table #work (tcid int, tcpid int)
create table #din(seq int identity ,tcid int ,tcpid int)declare @lvl int ,@curr int
select  @lvl=1,@curr=TC_ID from TreeClass where TC_Name=@TC_Nameinsert into #work values(@lvl,@curr)
while(@lvl>0) 
begin
  if exists (select * from #work where tcid=@lvl) 
     begin
          select top 1 @curr=tcpid from  #work where tcid=@lvl      insert #din (tcid,tcpid) values (@lvl,@curr)      delete #work
      where tcid=@lvl and tcpid=@curr
  
      insert #work
      select @lvl+1,TC_ID
      from TreeClass
      where TC_PID=@curr and TC_PID<>TC_ID     if(@@rowcount>0) set @lvl=@lvl+1
 end else
  set @lvl=@lvl-1
end
    
select replicate(char(9),tcid)+b.TC_Name
from #din a join TreeClass b on (a.tcpid=b.TC_ID)
order by seq
example：Get '美国'
 美国
  纽约
  旧金山Get '中国'
 中国
  北京
  上海
  江苏
   苏州
    常熟
   南京
   无锡
呵呵 方法比较笨 期待各位还有更好的解法

Answer 3

declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')
declare @lev int,@root int
declare @tmp table (TC_Id int)
declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp select TC_Id from @a where tc_pid=0while exists (select 1 from @tmp)
begin
  set @root=(select top 1 tc_id from @tmp)
  delete @tmp where tc_id=@root
  set @lev=0
  insert @tmp1 select *,@lev from @a where tc_ID=@root
  while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
  begin
    set @lev=@lev+1
    insert @tmp1 select a.*,@lev from  @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
  end
endselect REPLICATE(' ',lev)+tc_name from @tmp1

Answer 4

declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')
declare @lev int,@root int,@flag int
declare @tmp table (TC_Id int)
declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int,flag int)
insert @tmp select TC_Id from @a where tc_pid=0
set @flag=0while exists (select 1 from @tmp)
begin
  set @root=(select top 1 tc_id from @tmp)
  delete @tmp where tc_id=@root
  select @lev=0,@flag=@flag+1
  insert @tmp1 select *,@lev,@flag from @a where tc_ID=@root
  while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
  begin
    set @lev=@lev+1
    insert @tmp1 select a.*,@lev,@flag from  @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
  end
endselect REPLICATE(' ',lev)+tc_name from @tmp1---得到子叶节点
select tc_id,tc_pid,tc_name from @tmp1 BB where exists (select 1 from (select flag,max(lev) lev from @tmp1 group by flag)AA where AA.flag=BB.flag and aa.lev=bb.lev)

Answer 5

排序：
select *,dbo.fn_32gettopclass(Tc_id,default,1,default) from treeclass order by dbo.fn_32gettopclass(Tc_id,default,1,default)--------------
1 0 中国 '1'
4 1 北京 '1','4'
5 1 上海 '1','5'
6 1 江苏 '1','6'
10 6 无锡 '1','6','10'
7 6 苏州 '1','6','7'
8 7 常熟 '1','6','7','8'
9 6 南京 '1','6','9'
2 0 美国 '2'
11 2 纽约 '2','11'
12 2 旧金山 '2','12'
3 0 加拿大 '3'

Answer 6

所有叶节点select * from treeclass where tc_id not in (select tc_pid from treeclass)
----------3 0 加拿大
4 1 北京
5 1 上海
8 7 常熟
9 6 南京
10 6 无锡
11 2 纽约
12 2 旧金山

Answer 7

排序还是有点问题的。
更新函数：
CREATE FUNCTION FN_32GetTopClass (@InputId int,@IdStr varchar(8000)='',@type int=0,@LevelCount int=-1)  
/*
@Type= 0:得到顶层ID
1:得到当前到顶层的串
2:排序时使用
*/RETURNS Varchar(8000)
 AS  
BEGIN Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int,@OrderStr varchar(10)
if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else 
set @StartLevel=-1DECLARE TreeClass CURSOR local FOR 
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@FETCH_STATUS = 0
BEGIN if @type=1 or @type=2
begin
if @type=2 set @OrderStr='0000000000' else set @OrderStr=''
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+right(@OrderStr+cast(@tC_ID as varchar),10)+''''+@IdStr
end
else
if @TC_PID=0 select @IdStr=cast(@tC_ID as varchar) if @@NESTLEVEL<32
select @IdStr=dbo.FN_32GetTopClass (@TC_PID,@IdStr,@type,@LevelCount)
else
set @IdStr=@IdStr+'['+cast(@tC_ID as varchar)+']'
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PIDEndCLOSE TreeClass
DEALLOCATE TreeClasswhile @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,charindex('[',@Idstr)+1,charindex(']',@IdStr)-1-charindex('[',@Idstr))
set @IdStr=dbo.FN_32GetTopClass (@Id32,@IdStr,@type,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
endReturn @IdStrEND
---------------------------------
select *,dbo.fn_32gettopclass(Tc_id,default,2,default) from treeclass 
order by dbo.fn_32gettopclass(Tc_id,default,2,default)
---
1 0 中国 '0000000001'
4 1 北京 '0000000001','0000000004'
5 1 上海 '0000000001','0000000005'
6 1 江苏 '0000000001','0000000006'
7 6 苏州 '0000000001','0000000006','0000000007'
8 7 常熟 '0000000001','0000000006','0000000007','0000000008'
9 6 南京 '0000000001','0000000006','0000000009'
10 6 无锡 '0000000001','0000000006','0000000010'
2 0 美国 '0000000002'
11 2 纽约 '0000000002','0000000011'
12 2 旧金山 '0000000002','0000000012'
3 0 加拿大 '0000000003'