select from yourtable where newsclassid in ( select newsclassid from yourtable start with newsclassid = 'yourid' connect by prior newsclassid = parentclassid )给分吧
delete from yourtable where newsclassid in ( select newsclassid from yourtable start with newsclassid = 'yourid' connect by prior newsclassid = parentclassid )给分吧
这个问题我想用游标或临时标应该可以解决! 我这个有一个方法! create proc kkk @newsclassid int as begin declare @newsclassid varchar(20),@parentclassid varchar(20),@flag integer create table #temp(n_id varchar(20),p_id varchar(20)) select @flag=1 WHILE @flag >= 1 begin insert into #temp select newsclassid,parentclassid from tablename where parentclassid=@newsclassid delete form tablename where newsclassid=@newsclassid select top 1 @newsclassid=newsclassid,@parentclassid=parentclassid from #temp delete form tablename where newsclassid=@newsclassid and parentclassid=@parentclassid select count(*) into @flag from tablename end end
connect by prior newsclassid = parentclassid;
比如说所有子孙的ID都有一部分用来包含起祖先的ID信息
(
select newsclassid from yourtable start with newsclassid = 'yourid'
connect by prior newsclassid = parentclassid
)给分吧
(
select newsclassid from yourtable start with newsclassid = 'yourid'
connect by prior newsclassid = parentclassid
)给分吧
我用了一个function
归递的方法把下一级的ID用数组保存
这样不断地调用自身function
直到结束为止
目的达到了
哈哈
省了几百大洋
B的父类是C
C的父类是A
这不符合逻辑,但是可能会出现。你的递归会死机的。
我这个有一个方法!
create proc kkk @newsclassid int
as
begin
declare @newsclassid varchar(20),@parentclassid varchar(20),@flag integer
create table #temp(n_id varchar(20),p_id varchar(20))
select @flag=1
WHILE @flag >= 1
begin
insert into #temp
select newsclassid,parentclassid from tablename
where parentclassid=@newsclassid
delete form tablename where newsclassid=@newsclassid
select top 1 @newsclassid=newsclassid,@parentclassid=parentclassid
from #temp
delete form tablename where newsclassid=@newsclassid and parentclassid=@parentclassid
select count(*) into @flag from tablename
end
end