根據產品損耗率計算產品實際數量

原帖地址:

http://community.csdn.net/Expert/topic/3236/3236660.xml?temp=.9309046

表T1（記錄的是產品加工步驟的損耗情況）
Cp_No(產品編碼)   Cp_Step(加工步驟)         Cp_Shl(損耗率)
001                    1                       0.1
001                    2                       0.15
001                    3                       0.2
002                    1                       0.3
002                    2                       0.15
003                   ...                      ...

表T2（記錄產品經過加工步驟的最終數量）
Cp_No(產品編碼)       finally_Sl(最終數量)
 001                      5
 002                      7
 ...                     ...
要求：
根據T2表提供的最終數量以及T1表提供的損耗率，算出每個加工步驟的實際數量得到表T3
Cp_No(產品編碼)      Cp_Step(加工步驟)    Real_Sl(實際數量)
001                    1                  7.35/(1-0.1) =8.17
001                    2                  6.25/(1-0.15)=7.35
001                    3                  5/(1-0.2)    =6.25
002                    1                  8.24/(1-0.3) =11.77
002                    2                  7/(1-0.15)   =8.24

Cp_No是Varchar，Cp_Step是int,Cp_Shl是Numeric(18,4),Finally_Sl,Real_Sl是Numeric(18,4)
T2中的Finally_Sl 是經過T1中的所有加工步驟最終要得到的數量，比如001產品經過1，2，3三個步驟的最終數量是5。T3中的Real_Sl是由Finally_Sl根據每個加工步驟的損耗率得到,比如001由最終數量5可以得到步驟3的實際數量：5/(1-0.2)=6.25,然後根據6.25得到步驟2的實際數量6.25/(1-0.15)=7.35 

-----------------------------------------------------------------------------------------

--測試

--測試資料
create table T1(Cp_No varchar(10),Cp_Step int,Cp_Shl numeric(18,4))
insert T1 select '001',1,0.1
union all select '001',2,0.15
union all select '001',3,0.2
union all select '002',1,0.3
union all select '002',2,0.15

create table T2(Cp_No varchar(10),finally_Sl int)
insert T2 select '001',5
union all select '002',7
go

--方法1,直接計算(用輔助表)
select a.Cp_No,a.Cp_Step
 ,Cp_Shl=1-a.Cp_Shl,b.finally_Sl
 ,Real_Sl=cast(null as numeric(18,2))
into T3
from T1 a,T2 b
where a.Cp_No=b.Cp_No
order by a.Cp_No,a.Cp_Step desc

--計算 Real_Sl 列
declare @id varchar(10),@sl numeric(18,4)
update T3 set @sl=case @id when Cp_no then @sl else finally_Sl end/Cp_Shl
 ,Real_Sl=@sl,@id=Cp_no

--顯示處理結果
select Cp_No,Cp_Step,Real_Sl
from T3
order by Cp_No,Cp_Step
go

/*--測試結果

Cp_No      Cp_Step     Real_Sl  
---------- ----------- ----------
001        1           8.17
001        2           7.35
001        3           6.25
002        1           11.76
002        2           8.24

（所影響的行數為 5 行）
--*/

--方法2,寫自訂計算函數,實現直接出結果
--計算 Cp_Shl 的函數
create function f_calc(
@Cp_No varchar(10),
@Cp_Step int,
@finally_Sl int
)returns numeric(18,2)
as
begin
 declare @r numeric(18,2)
 set @r=@finally_Sl
 select @r=@r/(1-Cp_Shl) from T1
 where Cp_No=@Cp_No
  and Cp_Step>=@Cp_Step
 order by Cp_Step desc 
 return(@r)
end
go

--調用函數實現查詢
select a.Cp_No,a.Cp_Step
 ,Real_Sl=dbo.f_calc(a.Cp_No,a.Cp_Step,b.finally_Sl)
from T1 a,T2 b
where a.Cp_No=b.Cp_No
go

/*--測試結果

Cp_No      Cp_Step     Real_Sl  
---------- ----------- ----------
001        1           8.17
001        2           7.35
001        3           6.25
002        1           11.76
002        2           8.24

（所影響的行數為 5 行）
--*/

--刪除測試
drop table T1,T2,T3
drop function f_calc