请教一BOM存储过程（包含计算）
如题，想用存储过程实现图片效果。单纯的BOM结构查询容易实现，但这个包含计算的就有点难度了。
[解决办法]
1.递归
[解决办法]
- SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([modeid] int,modename varchar(20),parentid int)insert [tb]select 100 ,'商品管理', 0 union allselect 101 ,'定单管理', 0 union allselect 102 ,'用户管理', 0 union allselect 104 ,'学院广告', 0 union allselect 105 ,'系统设置', 0 union allselect 106 ,'附件管理', 0 union allselect 107 ,'商品管理', 100 union allselect 108 ,'明细管理', 100 union allselect 109 ,'物流管理', 100 union allselect 110 ,'商品信息管理', 107 union allselect 111 ,'商品分类管理', 107 union allselect 112 ,'回收站管理', 107 union allselect 114 ,'团购管理', 108 union allselect 115 ,'拍卖管理', 108 union allselect 116 ,'优惠管理', 108 union allselect 117 ,'会员管理', 102 union allselect 118 ,'会员卡管理', 102 union allselect 119 ,'资金管理', 102 union allselect 120 ,'管理员管理', 102 union allselect 121 ,'添加管理员', 120 union allselect 122 ,'修改管理员', 120go--查所有子结点if object_id('f_getC') is not null drop function f_getCgocreate function f_getC(@id int) returns @re table(id int,level int,sort varchar(10)) as begin declare @l int set @l=0 insert @re select @id,@l,null while @@rowcount>0 begin set @l=@l+1 insert @re select a.modeid,@l,ltrim(isnull(b.sort,a.modeid)) from tb as a,@re as b where b.id=a.parentid and b.level=@l-1 end update @re set level = level -1 return end go select a.modeid,a.parentid,REPLICATE(' ',b.level) +'┝'+a.modename,b.level,b.sort from tb a,f_getC(0) b where a.modeid=b.id order by case when b.level<2 then 0 else 1 end,b.sort,b.level/*modeid parentid sort level ----------- ----------- -------------------------------------------------- ---------- -----------100 0 ┝商品管理 100 0107 100 ┝商品管理 100 1108 100 ┝明细管理 100 1109 100 ┝物流管理 100 1101 0 ┝定单管理 101 0102 0 ┝用户管理 102 0117 102 ┝会员管理 102 1118 102 ┝会员卡管理 102 1119 102 ┝资金管理 102 1120 102 ┝管理员管理 102 1104 0 ┝学院广告 104 0105 0 ┝系统设置 105 0106 0 ┝附件管理 106 0110 107 ┝商品信息管理 100 2111 107 ┝商品分类管理 100 2112 107 ┝回收站管理 100 2114 108 ┝团购管理 100 2115 108 ┝拍卖管理 100 2116 108 ┝优惠管理 100 2121 120 ┝添加管理员 102 2122 120 ┝修改管理员 102 2（所影响的行数为 21 行）*/--查所有子结点,带路径与排序if object_id('f_getC') is not null drop function f_getCgocreate function f_getC(@id int) returns @re table(id int,level int,sort varchar(100),path varchar(500)) as begin declare @l int set @l=0 insert @re select [modeid],@l,right('00000'+ltrim(modeid),5),modename from tb where parentid=@id while @@rowcount>0 begin set @l=@l+1 insert @re select a.modeid,@l,b.sort+right('00000'+ltrim(a.modeid),5), b.path+' - '+a.modename from tb as a,@re as b where b.id=a.parentid and b.level=@l-1 end update @re set level = level return end go select a.modeid,a.parentid,REPLICATE(' ',b.level) +'┝'+a.modename,b.level,b.sort ,b.path from tb a,f_getC(0) b where a.modeid=b.id order by sort/*modeid parentid level ----------- ----------- -------------------- ----------- -------------------- ----------------------------------------100 0 ┝商品管理 0 00100 商品管理107 100 ┝商品管理 1 0010000107 商品管理 - 商品管理110 107 ┝商品信息管理 2 001000010700110 商品管理 - 商品管理 - 商品信息管理111 107 ┝商品分类管理 2 001000010700111 商品管理 - 商品管理 - 商品分类管理112 107 ┝回收站管理 2 001000010700112 商品管理 - 商品管理 - 回收站管理108 100 ┝明细管理 1 0010000108 商品管理 - 明细管理114 108 ┝团购管理 2 001000010800114 商品管理 - 明细管理 - 团购管理115 108 ┝拍卖管理 2 001000010800115 商品管理 - 明细管理 - 拍卖管理116 108 ┝优惠管理 2 001000010800116 商品管理 - 明细管理 - 优惠管理109 100 ┝物流管理 1 0010000109 商品管理 - 物流管理101 0 ┝定单管理 0 00101 定单管理102 0 ┝用户管理 0 00102 用户管理117 102 ┝会员管理 1 0010200117 用户管理 - 会员管理118 102 ┝会员卡管理 1 0010200118 用户管理 - 会员卡管理119 102 ┝资金管理 1 0010200119 用户管理 - 资金管理120 102 ┝管理员管理 1 0010200120 用户管理 - 管理员管理121 120 ┝添加管理员 2 001020012000121 用户管理 - 管理员管理 - 添加管理员122 120 ┝修改管理员 2 001020012000122 用户管理 - 管理员管理 - 修改管理员104 0 ┝学院广告 0 00104 学院广告105 0 ┝系统设置 0 00105 系统设置106 0 ┝附件管理 0 00106 附件管理(21 行受影响)*/参考看了楼主的这个图片，这个帖子估计无解了
[解决办法]
- SQL code
--随手写的，没测，我也不知道对不对，LZ多用些数据测测看。。。if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([ID] [int] IDENTITY(1,1) not null, strPH1 nvarchar(20), strPH2 nvarchar(20) ,strMQ nvarchar(20) , nstrDW nvarchar(5) , numYL numeric(18,2) , numHL numeric(18,2))insert [tb]select '01' , '02' , N'外壳', N'个' , 1 , 0 union allselect '02' , '03' , N'外壳按键', N'粒' , 6 , 0 union allselect '01' , '04' , N'显像管', N'个' , 1 , 0.03 union allselect '01' , '05' , N'电路板', N'块' , 1 , 0 union allselect '05' , '06' , N'元件', N'个' , 2 , 0.01 union allselect '05' , '07' , N'电阻', N'个' , 10 , 0 union allselect '07' , '08' , N'三级管', N'个' , 5 , 0.02goif object_id('usp_bom') is not null drop proc usp_bomGOCreate proc usp_bom (@parent nvarchar(20)) as begin set nocount on declare @level int ,@i int ,@flag int declare @bom table ( parent nvarchar(20),child nvarchar(20),level int, row int , flag int, strMQ nvarchar(20) , nstrDW nvarchar(5) , numYL numeric(18,2) , numHL numeric(18,2),numJG numeric(18,2)) select @level = 1,@i=1,@flag=1 insert @bom ( parent ,child ,level , row , flag , strMQ , nstrDW , numYL , numHL) select strPH1 ,strPH2, @level,0,1 ,strMQ , nstrDW , numYL , numHL from [tb] where strPH1 = @parent and strPH2 is not null while @level > 0 begin if exists (select * from @bom where level = @level and flag=1) begin select @parent = min(child) from @bom where level = @level and flag=1 update @bom set flag =0 , row=@i where level = @level and child = @parent and flag =1 select @i = @i +1 insert @bom ( parent ,child ,level , row , flag , strMQ , nstrDW , numYL , numHL) select strPH1,strPH2, @level + 1,0,1 ,strMQ , nstrDW , numYL , numHL from [tb] where strPH1 = @parent and strPH2 is not null if @@rowcount > 0 select @level = @level + 1 end else begin select @level = @level - 1 end end Declare @maxlevel int select @maxlevel=max(level) from @bom while @maxlevel>0 begin Update T set numJG = case when @maxlevel +1 =(select top 1 [level] from @bom as A where A.row = T.row+1) then numYL * isnull((select sum(numJG) from @bom as B where level=@maxlevel+1 and B.row>T.row and B.row<= isnull((select min(C.row) from @bom as C where level<>@maxlevel+1 and C.row>T.row),B.row+1) -1 ) ,0)/ isnull((select min(D.row) from @bom as D where level<>@maxlevel+1 and D.row>T.row) - T.row -1,1) else numYL * numHL end from @bom as T where level= @maxlevel set @maxlevel=@maxlevel-1 end --check result select row as ID,level as Che, parent ,child as strPH,strMQ , nstrDW , numYL , numHL,numJG from @bom order by row set nocount off end go --resultexec usp_bom '01'/*ID Che parent strPH strMQ nstrDW numYL numHL numJG------------------------------------------------1 1 01 02 外壳 个 1.00 0.00 0.002 2 02 03 外壳按键 粒 6.00 0.00 0.003 1 01 04 显像管 个 1.00 0.03 0.034 1 01 05 电路板 块 1.00 0.00 0.515 2 05 06 元件 个 2.00 0.01 0.026 2 05 07 电阻 个 10.00 0.00 1.007 3 07 08 三级管 个 5.00 0.02 0.10*/
[解决办法]
- SQL code
with tab as(select 1 as level,*,rank() over(order by strph2) as num1 from tb where strph1='01'union allselect level=level+1,b.*,a.num1 from tb b join tab a on a.strph2=b.strph1),taa as(select *,(case when row=1 then numyl*numhl end) as total from (select *,rank() over(partition by num1 order by level desc)as row from tab )a), taz as(select *,levelsamll=0 from taa where row=1union allselect b.*,levelsamll=1 from taa b join taz a on a.strph1=b.strph2)----select * from taz ,tac as(select *,total1=( case when a.total is not null then a.total else (select sum(total)*a.numyl from taa b where b.row=1 and a.num1=b.num1 and a.strph2=b.strph1)end) from taa a)----select * from tac,tad as(select a.level,a.id,a.strph1,a.strph2,a.strmq,a.strdw,a.numyl,a.numhl,a.num1,a.row,(case when b.levelsamll is null then a.numyl*a.numhl else a.total1 end) as total1 from tac a left join taz b on a.id=b.id )select * from tad where total1 is not nullunion all select a.level,a.id,a.strph1,a.strph2,a.strmq,a.strdw,a.numyl,a.numhl,a.num1,a.row,sum(b.total1)/b.level from tad a join tad b on a.strph2=b.strph1 and a.level+1=b.level and a.num1=b.num1 and a.total1 is null group by a.level,a.id,a.strph1,a.strph2,a.strmq,a.strdw,a.numyl,a.numhl,a.num1,a.row,b.level