级联编码如何生成

[BoyzFather]

现有分类表,字段:TypeID(int) 类型ID,TypeParentTypeID(int)类型的父ID,strText(varchar)内容,strFullLevelCode(varchar)ID级联码
如:
TypeID TypeParentTypeID strText strFullLevelCode
1 0 a 1
2 0 b 2
10 1 c 1.10
20 2 d 2.20
30 10 e 1.10.30
40 30 f 1.10.30.40
.
.
.
问:strFullLevelCode字段里的值,如何用程序实现.
请大虾赐教

[zlp321002]

create table t (TypeID int,TypeParentTypeID int,strText varchar(10))
insert into t select 1,0,'a'
union all select 2,0,'b'
union all select 10,1,'c'
union all select 20,2,'d'
union all select 30,10,'e'
union all select 40,30,'f';
go
create function f_getstr(@TypeID int)
returns varchar(20)
as
begin
declare @returns varchar(20)
set @returns='';
with cte_t(TypeID,TypeParentTypeID,lev)
as
(
select TypeID,TypeParentTypeID,0
from t where TypeID=@TypeID
union all
select a.TypeID,a.TypeParentTypeID,b.lev+1
from t a inner join cte_t b
on a.TypeID=b.TypeParentTypeID
)
select @returns=@returns+'.'+ltrim(TypeID) from cte_t
order by TypeID
return stuff(@returns,1,1,'')
end
go
select TypeID,TypeParentTypeID,strFullLevelCode=dbo.f_getstr(TypeID) from t
go
drop function f_getstr
drop table t
go

/*
TypeID TypeParentTypeID strFullLevelCode
----------- ---------------- --------------------
1 0 1
2 0 2
10 1 1.10
20 2 2.20
30 10 1.10.30
40 30 1.10.30.40

(6 行受影响)

*/

[中国风]

strFullLevelCode(varchar)ID级联码
create table ta(TypeID int, TypeParentTypeID int, strText varchar(2) )
insert ta select 1, 0, 'a'
insert ta select 2, 0, 'b'
insert ta select 10, 1, 'c'
insert ta select 20, 2, 'd'
insert ta select 30, 10, 'e'
insert ta select 40, 30, 'f'

create function test_f (@id int)
returns varchar(1000)
as
begin
declare @name varchar(1000),@Type int
select @name=rtrim(TypeID),@Type=TypeParentTypeID from ta where TypeID=@id
return
case when @name is null then null
else isnull(dbo.test_f(@Type)+'.','')+@name
end
end

select dbo.test_f(TypeID) from ta


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
2
1.10
2.20
1.10.30
1.10.30.40

(6 行受影响)

[中国风]

select *,strFullLevelCode=dbo.test_f(TypeID)
from ta

TypeID TypeParentTypeID strText strFullLevelCode
----------- ---------------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 0 a 1
2 0 b 2
10 1 c 1.10
20 2 d 2.20
30 10 e 1.10.30
40 30 f 1.10.30.40

(6 行受影响)

[中国风]

用函数实现递归(层数不能超过32级)