declare @t table (ID varchar(2),NAME varchar(2),PID varchar(2))
insert into @t
select '1','A','0' union all
select '2','B','0' union all
select '11','AA','1' union all
select '12','AB','1' union all
select '11','AA','2' union all
select '12','AB','2' union all
select 'x1','xx','11' union all
select 'x1','xx','11' union all
select 'x2','x2','11' union all
select 'x1','xx','12' union all
select 'x2','x2','12' union all
select 'as','as','x2'

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 ='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.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
/*
|--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 all
select '11','AA','2' 

--这个11到底是在1下面还是在2下面？

------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(2),[NAME] varchar(2),[PID] varchar(2),[iLevel] int)
insert [tb]
select '1','A','0',0 union all
select '2','B','0',0 union all
select '11','AA','1',1 union all
select '12','AB','1',1 union all
select '11','AA','2',1 union all
select '12','AB','2',1 union all
select 'x1','xx','11',2 union all
select 'x1','xx','11',2 union all
select 'x2','x2','11',2 union all
select 'x1','xx','12',2 union all
select 'x2','x2','12',2 union all
select 'as','as','x2',3

--1）把ID=1这棵树显示出来
;with t1 as(
select * from tb where id='1'
union all
select a.* from tb a join t1 b on a.pid=b.id
)
select * from t1
/**
ID   NAME PID  iLevel
---- ---- ---- -----------
1    A    0    0
11   AA   1    1
12   AB   1    1
x1   xx   12   2
x2   x2   12   2
as   as   x2   3
x1   xx   11   2
x1   xx   11   2
x2   x2   11   2
as   as   x2   3

(10 行受影响)
**/

--2）把ID=1作为根目录，并且这棵树中重复的x1找出来
;with t1 as(
select * from tb where id='1'
union all
select a.* from tb a join t1 b on a.pid=b.id
)
select * from t1 t
where (
  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   2
x1   xx   11   2

(2 行受影响)
**/

------解决方案--------------------

for SQL2000的写法,
SQL code

create table fsu
(ID varchar(4), name varchar(4), PID varchar(4), iLevel int)

insert into fsu
select '1', 'A', '0', '0' union all
se