--测试数据
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 SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
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 SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
--测试
/* 原作者：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 行）
--*/
--测试
/* 原作者：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
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
以下写一个简单的，视具体要求稍做修改即可。
create table 表(levelid int,levelname char(2),parent int)
insert 表 select 1, 'AA' , 0
union all select 2 , 'BB' , 1
union all select 3 , 'CC' , 1
union all select 4 , 'DD' , 2
union all select 5 , 'EE' , 3
union all select 6 , 'FF', 5
create function bom (@name char(2))
returns @tb table (levelid int,levelname char(2),parent int)
as
begin
insert @tb select levelID,LevelName,parent from 表 where Levelname = @name
while @@rowcount > 0
insert @tb select levelID,LevelName,parent from 表
where parent in (select levelID from @tb)
and levelID not in (select levelID from @tb)
return
end
select * from dbo.bom('bb')
levelid levelname parent
----------- --------- -----------
2 BB 1
4 DD 2
（所影响的行数为 2 行）---------------------表的結構--------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bom]
GO
CREATE TABLE [dbo].[bom] (
[Parent1] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[Child1] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[Level1] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
----------------------------遞虧式 求Bom 結構
If Exists(Select * From SysObjects Where ID=Object_ID(N'[dbo].[ExtendBom1]') and objectproperty(id,N'isProcedure') =1 )
Drop Procedure [Dbo].[ExtendBom1]
Go
Create Procedure ExtendBom1
@Para Varchar(10)
as
Declare @i int
set @i=1
if @Para=''
begin
select * into #Bom from bom
truncate table #Bom
end
Declare @Parent1 varchar(10),@Child1 varchar(10),@Level1 Varchar(10)
Declare KK Cursor Local for
Select Parent1,Child1,Level1 From Bom Where rtrim(Parent1)=rtrim(@Para) order by parent1,child1
Open kk
Fetch Next From KK Into @Parent1,@Child1,@Level1
--Select @Parent1,@Child1,@Level1
While @@Fetch_Status=0
Begin
insert into #Bom
Select @Parent1,@Child1,@Level1
Exec ExtendBom1 @Child1
set @i=@i+1
Fetch Next From KK Into @Parent1,@Child1,@Level1
End
Close KK
Deallocate KK
if (Select count(*) from bom)=(Select count(*) from #bom) and @i=1
begin
Select * From #Bom
set @i=2
end
exec ExtendBom1 ''
-- 不经常写 额
create function func_bom
(@cPSPCode varchar(10),
@Count int
)
returns @t table
(
cPSPCode varchar(10),
cPSCode varchar(10),
Amount int
)
as
begin
;with cte_bom as
(
select cPSCode,iPSQuantity,0 as lv
from ProductStructures where cPSPCode = @cPSPCode
union all
select A.cPSCode,A.iPSQuantity*B.iPSQuantity,B.lv+1
from ProductStructures A
inner join cte_bom B
on A.cPSPCode = B.cPSCode
)
insert @t
select @cPSPCode as cPSPCode,cPSCode, @count * iPSQuantity
from cte_bom where cPSCode in('0001','0002','0003','0004','0005')
order by cPSCode
return
endgoselect * from dbo.func_bom('0091',10)go
/* 结果0091 0001 20
0091 0002 30
0091 0003 30
0091 0004 60
0091 0005 90*/
http://topic.csdn.net/u/20090224/13/f42ae4e7-23a4-4a9a-b4a3-cdee879352a8.html