找出指定根目录节点下某个子节点中存在重复的数据

在以下表结构中保留了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
   

6 个解决方案

#1

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下面？

#2

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'

;with depts as(
select * from @T
    where ID = '1'
union all
    select a.*
    from @T a, depts b
    where a.pid = b.ID
)
select * from depts
/*
ID   NAME PID
---- ---- ----
1    A    0
11   AA   1
12   AB   1
x1   xx   12
x2   x2   12
as   as   x2
x1   xx   11
x1   xx   11
x2   x2   11
as   as   x2
*/

#3

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 行受影响)
**/

#4

回复叶子老师：
11 在1和2下面都有，它同属于1和2两棵树

#5

回复“百年树人”老师，我是SQL 2000的写法

#6

for SQL2000的写法,

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

insert into fsu
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这棵树显示出来
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)
end

select * from #fsu_t1 order by iLevel

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
x1   xx   12   2
x2   x2   12   2
as   as   x2   3
as   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)
end

select * from #fsu_t2
where ID in
(select ID 
from #fsu_t2
group by ID
having count(*)>1)

ID   name PID  iLevel
---- ---- ---- -----------
x1   xx   11   2
x1   xx   11   2
x2   x2   11   2
x1   xx   12   2
x2   x2   12   2
as   as   x2   3
as   as   x2   3