[分享]在SQL Server中返回分類級(jí)別數(shù)，以及打印順序

goodidea

/*

主要部分均可創(chuàng)造成過程，或者函數(shù)

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

*/



/*創(chuàng)建臨時(shí)測(cè)試用表*/

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','氮?dú)?,'01'

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

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

go

/*

計(jì)算各分類的級(jí)別，0 為頂級(jí)，便于在列表框中按類似樹形顯示。

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

*/

declare @L int

set @L = 0 --頂級(jí)分類的級(jí)別

--修正子分類與父分類相同的分類，以及父分類錯(cuò)誤的分類

update #tblClassTest

set class_code_parent = ''

where class_code_parent = class_code

      or class_code_parent not in (select class_code from #tblClassTest )

--初始化分類級(jí)別

update #tblClassTest

set class_level = @L - 2

--更新頂級(jí)分類

update #tblClassTest

set class_level = @L , class_code_parent = ''

where class_code_parent = '' or   class_code_parent is null

while @@ROWCOUNT >0

begin

  --級(jí)別自增

  set @L = @L + 1

  --更新第 @L 級(jí)的分類

  update #tblClassTest

  set class_level = @l

  where class_level = -2 -- 此處重要以免循環(huán)定義從屬關(guān)系？

and class_code_parent in

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

end

--輸出孤立的分類，可能是由于循環(huán)定義從屬關(guān)系

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

begin

  select *

  from #tblClassTest

  where class_level < 0

  Raiserror ('有孤立的分類存在，可能是由于循環(huán)定義從屬關(guān)系,請(qǐng)將其中一個(gè)定義為頂級(jí)分類',16,1)

end

/*

結(jié)束 計(jì)算各分類的級(jí)別

*/

go



/*

按分類的級(jí)別，計(jì)算從上到下的打印順序，按照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