|
/*
主要部分均可创造成过程,或者函数
最后更新: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 |
|