日期:2014-05-17 浏览次数:20560 次
--测试数据
CREATE TABLE t_A
(ID int,name VARCHAR(6),ParentID int)
INSERT INTO t_A
SELECT 1,'aa',0
UNION ALL
SELECT 2,'bb',0
UNION ALL
SELECT 3,'cc',1
UNION ALL
SELECT 4,'dd',3
go
--求个节点下所有子节点:
create function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id int,desn varchar(10),parentid int ,lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from t_A where id=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from t_A a,@t b
where a.parentid=b.id and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+'-','')+ltrim(id) from @t order by lev
return @cids
end
go
--自定义函数方式实现子节点查询父节点
if OBJECT_ID('GetParentID') is not null drop function GetParentID
go
create function GetParentID(@ChildID int)
returns varchar(500)
as
begin
declare @t table(ParentID int)
insert into @t select Pare