日期:2014-05-18  浏览次数:20567 次

找出指定根目录节点下某个子节点中存在重复的数据
在以下表结构中保留了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 @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