[分享]在SQL Server中返回分类的子类列表

goodidea

/*创建临时测试用表*/

create table tblClassTest

(

class_code char (20)

, class_name varchar(50)

, class_code_parent char(20)

)

insert into tblClassTest (class_code,class_name,class_code_parent)

select '01','原材料',null

union all select '0101','高压聚乙烯','01'

union all select '0102','低压聚乙烯','01'

union all select '0103','线性聚乙烯','01'

union all select '0104','色母料','01'

union all select '0106','其他原材料','01'

union all select '0105','回收原料','0106'

union all select '0109','回收高压聚乙烯','0105'

union all select '0107','丁烷气','0106'

union all select '0108','氮气','01'

go

/*

fun_getClassCodeListWithChild

返回分类的子类列表

最后更新：2005/01/02 goodidea (myad@yeah.net)

@intOption = 1  返回其子类

@intOption = 2  返回该类

@intOption = 3  返回该类及其子类

*/

create function fun_getClassCodeListWithChildren (

@ItemClass char(20)

,@intOption smallint = 2

) returns @tblItemClass table (

class_code char (20)

, class_name varchar(50)

, class_code_parent varchar(50)

)

as

begin

insert into @tblItemClass

select class_code, class_name,class_code_parent from tblClassTest

where class_code = @ItemClass

while @@rowcount >0 and (@intOption & 1 = 1)

begin

insert into @tblItemClass

select class_code, class_name,class_code_parent from tblClassTest

where class_code_parent in (select class_code from @tblItemClass )

and  class_code not in (select class_code from @tblItemClass )

end

if (@intOption & 2 <> 2) delete @tblItemClass where class_code = @ItemClass

return

end



go

/*显示测试结果*/

select * from fun_getClassCodeListWithChildren('01', default)

select * from fun_getClassCodeListWithChildren('01', 1)

select * from fun_getClassCodeListWithChildren('01', 3)

/*删除测试表和函数*/

drop function fun_getClassCodeListWithChildren

drop table tblClassTest