高分请求BOM表的建立及其遍历算法!

[lsp69]

请求BOM表的建立及其遍历算法
要求以最简洁的算法,在SQL中不能用cursor

[lw1a2]

我找找，找到的话，明天给你

[lsqkeke]

引用：
--测试数据 深度排序
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END

--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/

[lsqkeke]

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO

--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/

[caiyunxia]

给一个例子：

--测试
/* 原作者：zjcxc(邹建) */


CREATE TABLE [dbo].[BOM_DETAIL] (
[PKID] [int] ,--IDENTITY (1, 1) NOT NULL ,
[BOM_HEAD_PKID] [int] NOT NULL ,
[CHILDREN_ITEM] [int] NOT NULL ,
[QTY] [decimal](10, 0) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[BOM_HEAD] (
[PKID] [int] ,--IDENTITY (1, 1) NOT NULL ,
[MASTER_ITEM] [int] NOT NULL ,
[QTY] [int] NOT NULL ,
[VER] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STATUS] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]

CREATE TABLE [dbo].[item] (
[item] [int] ,--IDENTITY (1, 1) NOT NULL ,
[brand] [nvarchar] (10) ,
[part_no] [nvarchar] (10)
) ON [PRIMARY]

insert item select 1 ,'A' ,'A1'
union all select 2 ,'B' ,'AAAAA'
union all select 3 ,'A' ,'AD'
union all select 4 ,'A' ,'SS'
union all select 5 ,'C' ,'123'
union all select 6 ,'C' ,'AAADSFD'
union all select 7 ,'D' ,'D22'
union all select 8 ,'C' ,'DDDD512'
union all select 9 ,'A' ,'AA3223'
union all select 10,'DD','356'

insert BOM_HEAD select 1,1,1,1,'使用中'
union all select 2,3,1,1,'使用中'
union all select 3,1,1,2,'停用'
union all select 4,6,1,1,'使用中'
union all select 5,8,1,1,'使用中'
union all select 6,2,1,1,'使用中' --加一条数据

insert BOM_DETAIL select 1, 1,2 ,1
union all select 2, 1,6 ,2
union all select 3, 2,1 ,1
union all select 4, 3,4 ,1
union all select 5, 3,5 ,1
union all select 6, 4,7 ,1
union all select 7, 4,8 ,1
union all select 8, 5,9 ,1
union all select 9, 5,10,1
union all select 10,6,6, 1 --加一条数据
go

--展开bom查询的函数
create function f_bom(
@item int
)returns @r table(
item int,
brand nvarchar(10),
part_no nvarchar(10),
QTY decimal(10,0),--取自BOM_DETAIL
level int,--层次
sid varchar(8000)--排序字段,通过这个来排序,可以体现出树形的层次
)
as
begin
declare @l int

set @l=0
insert @r select @item,brand,part_no,0,@l,right(10000+item,4)
from item
where item=@item
while @@rowcount>0
begin
set @l=@l+1
insert @r select i.item,i.brand,i.part_no,d.qty,@l,r.sid+','+right(10000+i.item,4)
from item i,BOM_HEAD h,BOM_DETAIL d,@r r
where r.level=@l-1
and r.item=h.MASTER_ITEM
and h.STATUS='使用中'
and h.PKID=d.BOM_HEAD_PKID
and d.CHILDREN_ITEM=i.item
end
return
end
go

--调用函数得到查询结果
select 层次=space(level*2)+'├─'
,item,brand,part_no,qty
from f_bom(1)
order by sid

go
drop table item,BOM_HEAD,BOM_DETAIL
drop function f_bom

/*--测试结果

层次 item brand part_no qty
-------------- ---------- ---------- ---------- -------
├─ 1 A A1 0
├─ 2 B AAAAA 1
├─ 6 C AAADSFD 1
├─ 7 D D22 1
├─ 8 C DDDD512 1
├─ 9 A AA3223 1
├─ 10 DD 356 1
├─ 6 C AAADSFD 2
├─ 7 D D22 1
├─ 8 C DDDD512 1
├─ 9 A AA3223 1
├─ 10 DD 356 1

（所影响的行数为 12 行）
--*/

--处理示例

--示例数据
create table T1(Pid int,subPid int,Quantity int)
insert T1 select 102,104,2
union all select 102,105,2
union all select 104,106,3
union all select 104,109,1
union all select 105,107,3
union all select 107,108,1
go

--查询处理函数
create function f_cid(@subPid int)
returns @re table(subPid int,Quantity int,level int)
as
begin
declare @l int
set @l=0
insert @re select subPid,Quantity,@l
from T1
where Pid=@subPid
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.subPid,a.Quantity*b.Quantity,@l
from T1 a,@re b
where a.Pid=b.subPid and b.level=@l-1
end
delete a from @re a
where exists(
select * from T1 where Pid=a.subPid)
return
end
go

--调用实现查询
select subPid,Quantity from f_cid(102)
go

--删除测试
drop table T1
drop function f_cid

/*--结果

subPid Quantity
----------- -----------
106 6
109 2
108 6

（所影响的行数为 3 行）
--*/
zjcxc(邹建) ( ) 信誉：553
create table T1(Pid int,subPid int,Quantity int)
insert T1 select 102,104,2
union all select 102,105,2
union all select 104,106,3
union all select 104,109,1
union all select 105,107,3
union all select 107,108,1
go

--查询处理函数
create function f_cid(@subPid int)---建立函數，它返回一個表
returns @re table(subPid int,Quantity int,level int)--這裡的level，是用來存放樹的級樹的
as
begin
declare @l int
set @l=0
insert @re select subPid,Quantity,@l--假如它已經是根，級數為0
from T1
where Pid=@subPid
while @@rowcount>0--有記錄插入就執行循環
begin
set @l=@l+1
insert @re select a.subPid,a.Quantity*b.Quantity,@l
from T1 a,@re b
where a.Pid=b.subPid and b.level=@l-1
end
delete a from @re a
where exists(
select * from T1 where Pid=a.subPid)
return
end
go

--调用实现查询
select subPid,Quantity from f_cid(102)
go

--删除测试
drop table T1
drop function f_cid

/*--结果

subPid Quantity
----------- -----------
106 6
109 2
108 6

（所影响的行数为 3 行）
--*/
----老師的思想是通過建立一個表的字段來存放樹的級，通過這個級來得到葉點（即最基本的元素）
然後刪除非葉點記錄
--得到的就是你要的結果了

--查询函数中本来就已经包含了所有的单位所需要的个数

--查询处理函数
create function f_cid(@subPid int)
returns @re table(subPid int,Quantity int,level int)
as
begin
declare @l int
set @l=0
insert @re select subPid,Quantity,@l
from T1
where Pid=@subPid
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.subPid,a.Quantity*b.Quantity,@l
from T1 a,@re b
where a.Pid=b.subPid and b.level=@l-1
end
/*--这句删除的作用就是只保留最小单位,如果需要中间单位,去掉这个就可以了
delete a from @re a
where exists(
select * from T1 where Pid=a.subPid)
--*/
return
end
go
--也可以加一个层数来控制只计算到那一层

--查询处理函数
create function f_cid(@subPid int,@level int) --@level是控制查询到那一层,如果为-1,则查询所有层
returns @re table(subPid int,Quantity int,level int)
as
begin
declare @l int
set @l=0
insert @re select subPid,Quantity,@l
from T1
where Pid=@subPid
while @@rowcount>0 and (@l<@level or @level=-1)
begin
set @l=@l+1
insert @re select a.subPid,a.Quantity*b.Quantity,@l
from T1 a,@re b
where a.Pid=b.subPid and b.level=@l-1
end
/*--这句删除的作用就是只保留最小单位,如果需要中间单位,去掉这个就可以了
--如果在@level=-1时才删除,可以加上条件判断
-- if @level=-1
delete a from @re a
where exists(
select * from T1 where Pid=a.subPid)
--*/
return
end
go

[caiyunxia]

CrazyFor(蚂蚁)
http://expert.csdn.net/Expert/topic/1343/1343007.xml?temp=.730694

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_GetSubClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_GetSubClass]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_GetTopClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_GetTopClass]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TreeClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TreeClass]
GO

CREATE 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]
GO

ALTER TABLE [dbo].[TreeClass] WITH NOCHECK ADD
CONSTRAINT [PK_TreeClass] PRIMARY KEY CLUSTERED
(
[TC_id]
) ON [PRIMARY]
GO



SET IDENTITY_INSERT TreeClass on
Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (1, 0, '', '中国' )
Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (2, 0, '', '美国' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (3, 0, '', '加拿大' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (4, 1, '', '北京' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (5, 1, '', '上海' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (6, 1, '', '江苏' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (7, 6, '', '苏州' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (8, 7, '', '常熟' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (9, 6, '', '南京' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (10, 6, '', '无锡' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (11, 2, '', '纽约' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (12, 2, '', '旧金山' )

SET IDENTITY_INSERT TreeClass off

Go


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION FN_GetSubClass (@InputId int,@IdStr varchar(8000))

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int

If @IdStr='' Set @IdStr=''''+cast(@InputId as varchar)+''''

DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_PID=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@FETCH_STATUS = 0
BEGIN
select @IdStr=@IdStr+','+''''+cast(@tC_ID as varchar)+''''

select @IdStr=dbo.FN_GetSubClass (@TC_ID,@IdStr)


FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

Return @IdStr

END

[caiyunxia]

O
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION FN_GetTopClass (@InputId int,@IdStr varchar(8000),@type int)

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int

DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@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)

select @IdStr=dbo.FN_GetTopClass (@TC_PID,@IdStr,@type)


FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

Return @IdStr

END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



例：
--得到ID为1的所有下层类别ID串
select dbo.fn_getsubclass(1,'')
--查询ID为1的所有下层记录
select * from treeclass where charindex(''''+cast(TC_id as varchar)+'''',dbo.fn_getsubclass(1,''))>0
--得到ID为10顶层ID
select dbo.fn_gettopclass(10,'',0)
--得到提供ID所在枝的所有ID
select dbo.fn_getsubclass(dbo.fn_gettopclass(10,'',0),'')
--得到当前ID到顶层的ID串
select dbo.fn_gettopclass(10,'',1)




前一镇，写了两个递归的树型结构处理函数：
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]
GO

CREATE 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]
GO

ALTER 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]
GO

if 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]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE 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 int

if @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=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@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_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

while @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)+']','')
end

Return @IdStr

END

[caiyunxia]

更新：

CREATE FUNCTION FN_32GetTopClass (@InputId int,@IdStr varchar(8000)='',@type int=0,@LevelCount int=-1)
/*
@Type= 0:得到顶层ID
1:得到当前到顶层的串
2:排序时使用
*/

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int,@OrderStr varchar(10)


if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1

DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@FETCH_STATUS = 0
BEGIN

if @type=1 or @type=2
begin
if @type=2 set @OrderStr='0000000000' else set @OrderStr=''
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+right(@OrderStr+cast(@tC_ID as varchar),10)+''''+@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_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

while @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)+']','')
end

Return @IdStr

END

[caiyunxia]

sky_blue(老衲)


create table new_tree ( parent varchar(80), child varchar(80),weight int)
go
insert new_tree values ( '1','2',3);
insert new_tree values ( '1','3',4);
insert new_tree values ( '2','4',3);
insert new_tree values ( '2','5',5);
insert new_tree values ( '3','6',7);
insert new_tree values ( '3','7',3);
insert new_tree values ( '3','8',2);
insert new_tree values ( '6','9',1);
insert new_tree values ( '5','10',4);
insert new_tree values ( '4','11',3);
insert new_tree values ( '9','12',7);
insert new_tree values ( '7',NULL,1);
insert new_tree values ( '8',NULL,4);
insert new_tree values ( '10',NULL,3);
insert new_tree values ( '11',NULL,3);
insert new_tree values ( '12',NULL,7);

广度优先：
create proc proc_new_tree (@parent varchar(80),@mode int =0)
as

begin
set nocount on
declare @level int
declare @tmp1 table ( parent varchar(80), child varchar(80),level int)
select @level =1
insert @tmp1 select parent,child,@level from new_tree where parent = @parent
while exists(select * from @tmp1 where child is not NULL and level=@level)
begin
insert @tmp1 select a.parent,a.child ,@level+1 from new_tree a,@tmp1 b where a.parent = b.child and b.level=@level
select @level=@level +1
end
if @mode =0 select * from @tmp1
else select * from @tmp1 where child is null
set nocount off
end
go

深度优先：
create proc proc__tree (@parent char(20))
as
begin
set nocount on
declare @level int ,@i int ,@flag int
declare @stack table (parent char(20),child char(20),level int,row int , flag int)
select @level = 1,@i=1,@flag=1
insert @stack select parent,child, @level,0,1 from new_tree where parent = @parent and child is not null

while @level > 0
begin
if exists (select * from @stack where level = @level and flag=1)
begin
select @parent = min(child) from @stack where level = @level and flag=1
update @stack set flag =0 , row=@i where level = @level and child = @parent and flag =1
select @i = @i +1
insert @stack select parent,child, @level + 1,0,1 from new_tree where parent = @parent and child is not null
if @@rowcount > 0
select @level = @level + 1
end
else
begin
select @level = @level - 1
end
end
select row,parent ,child,level from @stack order by row
set nocount off
end
go
proc__tree '1'

[lcooc]

高手啊,收了