找精华贴
《 化解32层以上嵌套,树型结构的递归实现方法,给大家参考一下! 》(CrazyFor )csdn怎么搞的,精华贴都给转移得不见了是不是不想发工资了本ceo决定取消这个月的所有奖金。
《 化解32层以上嵌套,树型结构的递归实现方法,给大家参考一下! 》(CrazyFor )csdn怎么搞的,精华贴都给转移得不见了是不是不想发工资了本ceo决定取消这个月的所有奖金。
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')
declare @lev int,@root int
declare @tmp table (TC_Id int)
declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp select TC_Id from @a where tc_pid=0while exists (select 1 from @tmp)
begin
set @root=(select top 1 tc_id from @tmp)
delete @tmp where tc_id=@root
set @lev=0
insert @tmp1 select *,@lev from @a where tc_ID=@root
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
begin
set @lev=@lev+1
insert @tmp1 select a.*,@lev from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
end
endselect REPLICATE(' ',lev)+tc_name from @tmp1
<化解32层以上嵌套,树型结构的递归实现方法,给大家参考一下>(CrazyFor )前一镇,写了两个递归的树型结构处理函数:
http://expert.csdn.net/Expert/topic/1343/1343007.xml?temp=.730694后来有朋友提出32层以上嵌套的一个展BOM的实例,没有办法用递归实现,特想了个办法,实现32层以上树型结构的递归方法。现在特将以前的那个函数进行了改进,具体如下:---------------------------------表及函数脚本if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TreeClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TreeClass]
GOCREATE TABLE [dbo].[TreeClass] (
[TC_id] [int] IDENTITY (1, 1) NOT NULL ,
[TC_PID] [int] NOT NULL ,
[TC_OtherTypeID] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
[TC_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[TreeClass] WITH NOCHECK ADD
CONSTRAINT [PK_TreeClass] PRIMARY KEY CLUSTERED
(
[TC_id]
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_32GetSubClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_32GetSubClass]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_32GetTopClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_32GetTopClass]
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE FUNCTION FN_32GetSubClass (@InputId int,@IdStr varchar(8000)='',@LevelCount int=-1)
/*
参数: @InputId,被搜索子类的ID
@IdStr,一个特殊参数,用于在递归中传数据,注意:调用函数时一定要传入‘’空值
@LevelCount 用于判断是不是递归调用的开始层
*/
RETURNS Varchar(8000)
AS
BEGIN Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 intif @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1
If @IdStr='' Set @IdStr=''''+cast(@InputId as varchar)+''''DECLARE TreeClass CURSOR local FOR --定义游标
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_PID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@FETCH_STATUS = 0 --循环游标,即循环当前类的弟一级子类
BEGIN
select @IdStr=@IdStr+','+''''+cast(@tC_ID as varchar)+''''
if @@NESTLEVEL<32
set @IdStr=dbo.FN_32GetSubClass (@TC_ID,@IdStr,@LevelCount) --递归,自己调用自己。
else
set @IdStr='['+cast(@tC_ID as varchar)+']'+@IdStr
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PIDEndCLOSE TreeClass
DEALLOCATE TreeClasswhile @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,2,charindex(']',@IdStr)-2)
set @IdStr=dbo.FN_32GetSubClass (@Id32,@IdStr,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
endReturn @IdStrENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE FUNCTION FN_32GetTopClass (@InputId int,@IdStr varchar(8000)='',@type int=0,@LevelCount int=-1) RETURNS Varchar(8000)
AS
BEGIN Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int
if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@FETCH_STATUS = 0
BEGIN
if @type=1
begin
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+cast(@tC_ID as varchar)+''''+@IdStr
end
else
if @TC_PID=0 select @IdStr=cast(@tC_ID as varchar) if @@NESTLEVEL<32
select @IdStr=dbo.FN_32GetTopClass (@TC_PID,@IdStr,@type,@LevelCount)
else
set @IdStr=@IdStr+'['+cast(@tC_ID as varchar)+']'
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PIDEndCLOSE TreeClass
DEALLOCATE TreeClasswhile @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,charindex('[',@Idstr)+1,charindex(']',@IdStr)-1-charindex('[',@Idstr))
set @IdStr=dbo.FN_32GetTopClass (@Id32,@IdStr,@type,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
endReturn @IdStrEND
GO
SET QUOTED_IDENTIFIER OFF
GO
SET
---------------------------------------------------------------
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.1570551/*--树形数据处理方案 树形数据的排序,新增,修改,复制,删除,数据完整性检查,汇总统计
--邹建 2003.9--*/
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp1 select *,1 from @a where tc_ID=1
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.*,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
select * from @tmp1/*--按子找父--*/
--建立环境
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200))--开始结点
insert @tmp1 select * from @a where tc_ID=10--循环得到
while exists(select 1 from @a a,@tmp1 b where a.tc_id=b.tc_pID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.* from @a a,@tmp1 b where a.tc_id=b.tc_pID and a.tc_ID not in (select tc_ID from @tmp1)--显示结果
select * from @tmp1/*--遍厉所有--*/
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200))
while exists(select 1 from @a where TC_PID=0 and tc_id not in (select tc_id from @tmp1))
begin
insert @tmp1 select top 1 * from @a where TC_PID=0 and tc_id not in (select tc_id from @tmp1) order by TC_Id
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.* from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
end
select * from @tmp1