本帖最后由 xxyping 于 2012-12-07 09:11:19 编辑 id pid 表1
0
01 0
02 0
03 0
0101 01
0102 01
0201 02
0301 03
0302 03
010101 0101
020101 0201
01010101 010101
030201 0302
030202 0302
id did 表2
1 01010101
1 03
1 0201
表1是一个树表
表2是查找限制表
想要找到所有树下面的最后一层节点
例如：id-0，这样我找到数据应该是01010101\030201\030202\020101
也就是说找的数据是根据表2来限制的，如果表2中有匹配的id为0的，则找到它下面所有的最后一层节点，否则就按照表2中的匹配关系，找到它的子节点下有设置关系的所有最后一层节点
假如id-01，则只能找到01010101；
03的话可以找到030201\030202
求高手给个取树方案，谢谢
------最佳解决方案--------------------
--那你试下这个，bom表太大的话，会比较慢，可以分2次展bom
declare @t1 table(id varchar(10),pid varchar(10))
insert into @t1 values('01','0')
insert into @t1 values('02','0')
insert into @t1 values('03','0')
insert into @t1 values('0101','01')
insert into @t1 values('0102','01')
insert into @t1 values('0201','02')
insert into @t1 values('0301','03')
insert into @t1 values('0302','03')
insert into @t1 values('010101','0101')
insert into @t1 values('020101','0201')
insert into @t1 values('01010101','010101')
insert into @t1 values('030201','0302')
insert into @t1 values('030202','0302')
declare @t2 table(id int, did varchar(10))
insert into @t2 values(1,'01010101')
insert into @t2 values(2,'03')
insert into @t2 values(3,'0201')
declare @id varchar(10)
set @id='0302'
;with cte_bom
as
(
select id,pid ,
rights= case when exists(select 1 from @t2 where did=A.pid )
then 1 else 0 end,
flag = case when pid=@id or id=@id then 1 else 0 end
from @t1 as A where not exists(select 1 from @t1 where id=A.pid)
union all
select A.id,A.pid,
case when cte_bom.rights =1 then 1 else
case when exists(select 1 from @t2 where did=A.pid )
then 1 else 0 end
end,
flag = case when flag=1 then 1 else
case when A.id=@id then 1 else 0 end
end
from cte_bom,@t1 as A
where cte_bom.id = A.pid
)
select id,pid
from cte_bom as A
where not exists(select 1 from @t1 where pid=A.id)
and (rights=1 or exists(select 1 from @t2 where did=A.id ) )
and flag=1
------其他解决方案--------------------