SQL Server没有像oracle中那样可以很方便的查找上下级的层次搜索的方法。我尝试使用了一个存储过程来实现该功能,存储过程是以前看到别人写过类似的拿来改的。
create proc getParentType (@type_id int)
as
begin
declare @tmp1 table ([ID] int, type_name varchar(100), parent_type_id int)
declare @tmp2 table ([ID] int, type_name varchar(100), parent_type_id int)
declare @tmp3 table ([ID] int, type_name varchar(100), parent_type_id int)
insert @tmp1 select * from TD_Type where [type_id]=@type_id
insert @tmp2 select * from TD_Type where [type_id]=@type_id
while (select count(*) from @tmp2)>0
begin
insert @tmp1 select * from TD_Type where type_id in (select [parent_type_id] from @tmp2)
insert @tmp3 select * from @tmp2
delete @tmp2
insert @tmp2 select * from TD_Type where type_id in (select [parent_type_id] from @tmp3)
delete @tmp3
insert @tmp1 select * from TD_Type where type_id in (select [parent_type_id] from @tmp2)
end
end
select distinct(id), type_name, parent_type_id from @tmp1
GO
------------------执行
exec getParentType 10015