在以下表结构中保留了N棵树，有的树可能只有1级，但有的有多级，如下示例数据：
ID NAME PID iLevel
1 A 0 0
2 B 0 0
11 AA 1 1
12 AB 1 1
11 AA 2 1
12 AB 2 1
x1 xx 11 2
x1 xx 11 2
x2 x2 11 2
x1 xx 12 2
x2 x2 12 2
as as x2 3
....
两个要求：
1）把ID=1这棵树显示出来，这棵树显示的结果应该是：
ID NAME PID iLevel
1 A 0 0
11 AA 1 1
12 AB 1 1
x1 xx 11 2
x1 xx 11 2
x2 x2 11 2
as as x2 2
2）我要把ID=1作为根目录，并且这棵树中重复的x1找出来,显示结果应该是：
ID NAME PID iLevel
x1 xx 11 2
x1 xx 11 2
------解决方案--------------------
- SQL code
declare @t table (ID varchar(2),NAME varchar(2),PID varchar(2))insert into @tselect '1','A','0' union allselect '2','B','0' union allselect '11','AA','1' union allselect '12','AB','1' union allselect '11','AA','2' union allselect '12','AB','2' union allselect 'x1','xx','11' union allselect 'x1','xx','11' union allselect 'x2','x2','11' union allselect 'x1','xx','12' union allselect 'x2','x2','12' union allselect 'as','as','x2'DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))DECLARE @Level intSET @Level=0INSERT @t_Level SELECT ID,@Level,IDFROM @tWHERE PID ='0'WHILE @@ROWCOUNT>0BEGIN SET @[email protected]+1 INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID FROM @t a,@t_Level b WHERE a.PID=b.ID AND [email protected]END--显示结果SELECT SPACE(b.Level*2)+'|--'+a.NameFROM @t a,@t_Level bWHERE a.ID=b.ID ORDER BY b.Sort/*|--A |--AA |--AA |--xx |--xx |--xx |--xx |--xx |--xx |--x2 |--x2 |--as |--AB |--AB |--xx |--xx |--xx |--x2 |--x2 |--as|--B |--AA |--AA |--xx |--xx |--xx |--xx |--xx |--xx |--x2 |--x2 |--as |--AB |--AB |--xx |--xx |--xx |--x2 |--x2 |--as*/--同个节点下的相同节点select ID,NAME,PID from @t group by ID,NAME,PID having(count(1)>1)/*ID NAME PID---- ---- ----x1 xx 11*/--数据有问题，例如select '11','AA','1' union allselect '11','AA','2' --这个11到底是在1下面还是在2下面？
------解决方案--------------------
- SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([ID] varchar(2),[NAME] varchar(2),[PID] varchar(2),[iLevel] int)insert [tb]select '1','A','0',0 union allselect '2','B','0',0 union allselect '11','AA','1',1 union allselect '12','AB','1',1 union allselect '11','AA','2',1 union allselect '12','AB','2',1 union allselect 'x1','xx','11',2 union allselect 'x1','xx','11',2 union allselect 'x2','x2','11',2 union allselect 'x1','xx','12',2 union allselect 'x2','x2','12',2 union allselect 'as','as','x2',3--1）把ID=1这棵树显示出来;with t1 as(select * from tb where id='1'union allselect a.* from tb a join t1 b on a.pid=b.id)select * from t1/**ID NAME PID iLevel---- ---- ---- -----------1 A 0 011 AA 1 112 AB 1 1x1 xx 12 2x2 x2 12 2as as x2 3x1 xx 11 2x1 xx 11 2x2 x2 11 2as as x2 3(10 行受影响)**/--2）把ID=1作为根目录，并且这棵树中重复的x1找出来;with t1 as(select * from tb where id='1'union allselect a.* from tb a join t1 b on a.pid=b.id)select * from t1 twhere ( select count(1) from t1 where id=t.id and name=t.name and pid=t.pid and ilevel=ilevel and id='x1')>1/**ID NAME PID iLevel---- ---- ---- -----------x1 xx 11 2x1 xx 11 2(2 行受影响)**/
------解决方案--------------------
for SQL2000的写法,
- SQL code
create table fsu(ID varchar(4), name varchar(4), PID varchar(4), iLevel int)insert into fsuselect '1', 'A', '0', '0' union allselect '2', 'B', '0', '0' union allselect '11', 'AA', '1', '1' union allselect '12', 'AB', '1', '1' union allselect '11', 'AA', '2', '1' union allselect '12', 'AB', '2', '1' union allselect 'x1', 'xx', '11', '2' union allselect 'x1', 'xx', '11', '2' union allselect 'x2', 'x2', '11', '2' union allselect 'x1', 'xx', '12', '2' union allselect 'x2', 'x2', '12', '2' union allselect 'as', 'as', 'x2', '3'-- 1）把ID=1这棵树显示出来select * into #fsu_t1 from fsu where ID='1'while(@@rowcount>0)begin insert into #fsu_t1 select a.* from fsu a inner join #fsu_t1 b on a.PID=b.ID and a.iLevel>b.iLevel and a.ID not in (select ID from #fsu_t1)endselect * from #fsu_t1 order by iLevelID name PID iLevel---- ---- ---- -----------1 A 0 011 AA 1 112 AB 1 1x1 xx 11 2x1 xx 11 2x2 x2 11 2x1 xx 12 2x2 x2 12 2as as x2 3as as x2 3-- 2）我要把ID=1作为根目录，并且这棵树中重复的x1找出来,select * into #fsu_t2 from fsu where ID='1'while(@@rowcount>0)begin insert into #fsu_t2 select a.* from fsu a inner join #fsu_t2 b on a.PID=b.ID and a.iLevel>b.iLevel and a.ID not in (select ID from #fsu_t2)endselect * from #fsu_t2where ID in(select ID from #fsu_t2group by IDhaving count(*)>1)ID name PID iLevel---- ---- ---- -----------x1 xx 11 2x1 xx 11 2x2 x2 11 2x1 xx 12 2x2 x2 12 2as as x2 3as as x2 3