日期:2014-05-18 浏览次数:20488 次
declare @t table (ID varchar(1),userName varchar(5),parentId varchar(1),status bit) insert into @t select 1,'user1',0,1 union all select 2,'user2',1,1 union all select 3,'user3',2,0 union all select 4,'user4',3,0 union all select 5,'user5',1,1 union all select 6,'user6',5,0 Declare @p varchar(6) set @p='user1' select id,userName from @T where parentId=(select id from @T where userName=@p) /* id userName ---- -------- 2 user2 5 user5 */ --不知道后面的 2 2 1 1 是怎么统计出来的... --深度排序显示处理 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 parentId =0 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.parentId=b.ID AND b.Level=@Level-1 END --显示结果 SELECT left(SPACE(b.Level*2)+'|--'+a.userName,16) as userName,a.status FROM @t a,@t_Level b WHERE a.ID=b.ID ORDER BY b.Sort /* userName status -------------------------------- ------ |--user1 1 |--user2 1 |--user3 0 |--user4 0 |--user5 1 |--user6 0 */ --你给出的结果:user2后面是2真2假 --user5后面是1真1假 --一共是6个数据,但是user1的真为什么算到2里面?