级联编码如何生成

Question

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 行受影响)*/

Answer 1

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
endselect dbo.test_f(TypeID) from ta
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
2
1.10
2.20
1.10.30
1.10.30.40(6 行受影响)

Answer 2

select *,strFullLevelCode=dbo.test_f(TypeID)
 from taTypeID      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 行受影响)