[分享]在SQL Server中返回分类级别数，以及打印顺序

goodidea

/*

主要部分均可创造成过程，或者函数

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

*/



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

set nocount on

create table #tblClassTest

(

class_code char (20) not null

, class_name varchar(50)

, class_code_parent char(20)

, class_level smallint not null default (0)

)

insert into #tblClassTest (class_code,class_name,class_code_parent)

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

union all select '02','辅助原材料',''

union all select '03','包装物',''

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 '010601','回收原料','0106'

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

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

union all select '01060101','回收高压聚乙烯','010601'

go

/*

计算各分类的级别，0 为顶级，便于在列表框中按类似树形显示。

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

*/

declare @L int

set @L = 0 --顶级分类的级别

--修正子分类与父分类相同的分类，以及父分类错误的分类

update #tblClassTest

set class_code_parent = ''

where class_code_parent = class_code

      or class_code_parent not in (select class_code from #tblClassTest )

--初始化分类级别

update #tblClassTest

set class_level = @L - 2

--更新顶级分类

update #tblClassTest

set class_level = @L , class_code_parent = ''

where class_code_parent = '' or   class_code_parent is null

while @@ROWCOUNT >0

begin

  --级别自增

  set @L = @L + 1

  --更新第 @L 级的分类

  update #tblClassTest

  set class_level = @l

  where class_level = -2 -- 此处重要以免循环定义从属关系？

and class_code_parent in

(select class_code from #tblClassTest  where class_level = @L -1)

end

--输出孤立的分类，可能是由于循环定义从属关系

if exists (select * from #tblClassTest  where class_level < 0)

begin

  select *

  from #tblClassTest

  where class_level < 0

  Raiserror ('有孤立的分类存在，可能是由于循环定义从属关系,请将其中一个定义为顶级分类',16,1)

end

/*

结束 计算各分类的级别

*/

go



/*

按分类的级别，计算从上到下的打印顺序，按照PLR遍历法，不使用递归

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

*/

/*

create function fn_Class()

returns @tblItemClass table (

class_code char (20)

, class_name varchar(50)

, class_code_parent char (20)

, class_level smallint

, class_order smallint default 0

)

as

*/

begin

declare @tblItemClass table (

class_code char (20)

, class_name varchar(50)

, class_code_parent char (20)

, class_level smallint

, class_order smallint default 0

)

Declare @RC smallint

Declare @O smallint

Declare @CA smallint

Declare @CB smallint

Declare @class_code char (20)

Declare @class_code_parent char (20)

select @RC = 0, @O = 1,  @CB = 1 ,@CA = @CB + 1

insert into @tblItemClass

select top 1 class_code, class_name, class_code_parent, class_level, @O

from #tblClassTest

where class_level = (select min(class_level) from #tblClassTest)

and class_code not in (select class_code from @tblItemClass)

set @RC = @RC + @@rowcount

select @CB = count(*) from @tblItemClass

set @class_code = (select top 1  class_code from @tblItemClass order by class_order desc)

set @class_code_parent = (select top 1  class_code_parent from @tblItemClass order by class_order desc)

print @class_code + '  P:' + @class_code_parent + cast (@O as char(5))

while @CA > @CB and @CB>0

begin

if @RC > 0 set @O = @O + 1

set @RC = 0

print 'Will insert  ' + @class_code + '''s first Child.   P:' + @class_code_parent + cast (@O as char(5))

insert into @tblItemClass

select top 1 class_code, class_name, class_code_parent, class_level, @O

from #tblClassTest

where class_code not in (sele