set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[f_cid](@BOMNO varchar(100)) returns @t_level table(P_CODE varchar(100),CODE varchar(100),CBDESC nvarchar(100),QTY_NEED numeric(19,8),WASTAGE numeric(6,2),level int,level1 int)
as
begin
declare @level int
declare @level1 int
set @level = 1
set @level1 = 0
insert into @t_level select P_CODE,CODE,CBDESC,QTY_NEED,(WASTAGE/100)WASTAGE,@level,@level1 from BOMT where BOMNO=@BOMNO
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.P_CODE,a.CODE,a.CBDESC,a.QTY_NEED,a.WASTAGE+(WASTAGE/100),@level,
case when (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)=0
then b.level1
else (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)+
(select max(level1) from @t_level)
end
from BOMT a , @t_level b
where a.P_CODE = b.CODE and b.level = @level - 1 and a.CODE<>@BOMNO
end
return
end
WASTAGE这个字段我要它叠加就是本层的数加上他上层的数作为参数插入。为什么这里得到的数据有问题。应该怎么写。
from BOMT a , @t_level b
上面的 BOMT a,表示本层引用的表,
@t_level b,表示上层引用的表
then b.level1
else (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)+
(select max(level1) from @t_level)
--case when (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)=0
是指本级别没有重复,那么继续沿用老的级别 then b.level1
否则 else (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)+
(select max(level1) from @t_level)
上面是指本级别有多个时,找到最大的级别后,再加上本级别的排名,得到的新的级别
set QUOTED_IDENTIFIER ON
goALTER function [dbo].[f_cid](@BOMNO varchar(100),@liqty varchar(100)) returns @t_level table(P_CODE varchar(100),CODE varchar(100),CBDESC nvarchar(100),QTY_NEED numeric(19,8),WASTAGE numeric(6,2),UNIT nvarchar(20),LIQYT numeric(19,8),level int,level1 int)
as
begin
declare @level int
declare @level1 int
set @level = 1
set @level1 = 0
insert into @t_level select P_CODE,CODE,CBDESC,QTY_NEED,(WASTAGE/100)WASTAGE,UNIT,ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED)LIQYT,@level,@level1 from BOMT where BOMT.BOMNO=@BOMNO
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.P_CODE,a.CODE,a.CBDESC,a.QTY_NEED,a.WASTAGE+(b.WASTAGE/100),a.UNIT,ceiling((b.LIQYT+(b.LIQYT*(a.WASTAGE/100)))*a.QTY_NEED)LIQYT,@level,
case when (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)=0
then b.level1
else (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)+
(select max(level1) from @t_level)
end
from BOMT a , @t_level b
where a.P_CODE = b.CODE and b.level = @level - 1 and a.CODE<>@BOMNO
end
return
end这样写没错吧,但是为什么他们层次会有问题呢?1 KJ-1111-003
2 ZC111100701
3 PSA07022300
3 POA10022200
4 POA07022300
5 PMA13000400这里他少了一个,POA10022200
还有些地方也是少了,要不就多了。
为什么会是这样?有些地方是对的有些地方是错的。你有SKYPE或者百度HI嘛?邮箱什么的能帮我解决不?
这条?
------------------------------------------------------------ ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------- ---------------------------------------
KJ-1111-003-01 KJ-1111-003 KJ-1111MCW GS/CE/LFGB/EMC/RoHS 220-240V~AC 50/60HZ. 750W 陶瓷白 1.00000000 0.000000 PCS 2915(1 行受影响)
SQL code
1 KJ-1111-003
2 ZC111100701
3 PSA07022300
3 POA10022200
4 POA07022300
5 PMA13000400这里他少了一个,POA10022200你上面的执行语句是什么?
会有三条记录之多P_CODE CODE
------------------------------------------------------------ ----------------------
KJ-1111-003-01 KJ-1111-003
KJ-1111-003-01 KJ-1111-003
KJ-1111-003-01 KJ-1111-003(3 行受影响)
嗯这个会有的,应为他不止一个记录在表里面,有几个,只不过他们的编号不一样,就是BOMNO,内容一样的。
--自定义函数[dbo].[f_cid],参数level用来分层,参数level1用来排序
create function [dbo].[f_cid](@BOMNO varchar(100),@liqty varchar(100))
returns @t_level table(P_CODE varchar(100),CODE varchar(100),
CBDESC nvarchar(100),QTY_NEED numeric(19,8),
WASTAGE numeric(6,2),UNIT nvarchar(20),LIQYT numeric(19,8),level int,level1 varchar(8000))
as
begin
declare @level int
declare @level1 int set @level = 1
set @level1 = '0' insert into @t_level select P_CODE,CODE,CBDESC,QTY_NEED,(WASTAGE/100)WASTAGE,UNIT,
ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED)LIQYT,@level,@level1
from BOMT where BOMT.BOMNO=@BOMNO
while @@ROWCOUNT > 0
begin
set @level = @level + 1 insert into @t_level select a.P_CODE,a.CODE,a.CBDESC,a.QTY_NEED,a.WASTAGE+(b.WASTAGE/100),a.UNIT,ceiling((b.LIQYT+(b.LIQYT*(a.WASTAGE/100)))*a.QTY_NEED)LIQYT,@level,
case when (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)=0
then b.level1+'-000'
else b.level1+'-'+right('00'+ltrim((select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>'WI-EGD-3575')),3)
end from BOMT a , @t_level b
where a.P_CODE = b.CODE and b.level = @level - 1 and a.CODE<>@BOMNO end
return
end
go
--根据自定义函数查询
select REPLICATE(' ',level-1)+ltrim(level) as 层次, P_CODE as 产品,CODE as 物料,level1,level
from f_cid('WI-EGD-3575','100') order by level1
--删除自定义函数
drop function [dbo].[f_cid]