/*
1.高效批量进行单位转换
2.实现按某一单位汇总
*/Declare @ToUOM BigInt --需要转换成的单位
Set @ToUOM=13--需要转换的数据,先放入临时表
If OBJECT_ID('tempdb..#DemoData') IS NOT NULL
    DROP TABLE #DemoData
Select ID,Item,UOM,Qty as '转换前',CONVERT(Decimal(24,9),0) as '转换后' Into #DemoData From Base_BizData--生成 Item和单位的临时表(Item,UOM)
If OBJECT_ID('tempdb..#ItemUOM') IS NOT NULL
    DROP TABLE #ItemUOM
Create Table #ItemUOM
(
    Item Nvarchar(50),
        FromUOM 
BigInt,--从单位
    FromBUOM BigInt,--从单位基准单位
    FromRatio Decimal(24,9),--从单位到从基准单位转换率
    
    ToUOM BigInt,--到单位
    ToBUOM BigInt,--到单位基准单位
    ToRatio Decimal(24,9),--到单位到从基准单位转换率
    
    Ratio Decimal(24,9),--转换率
    BRatio Decimal(24,9)--基准单位转换率
)
Insert Into #ItemUOM(Item,FromUOM,ToUOM) Select Item,UOM,@ToUOM From #DemoData Group By Item,UOM--用with生成组内单位转换率临时表
If OBJECT_ID('tempdb..#UOMRatio') IS NOT NULL
    DROP TABLE #UOMRatio
;With UOMTree (Class,FromUOM,ToUOM,Ratio) AS
(
    Select Class,ID,BUOM,Ratio From Base_UOM Where ID=BUOM    --ID=BUOM表示基准单位
    union all
    Select A.Class,A.ID,B.ToUOM,CONVERT(Decimal(24,9),A.Ratio*B.Ratio) From Base_UOM as A
    Inner Join UOMTree as B on A.BUOM=B.FromUOM Where ID!=BUOM    --ID=BUOM表示非基准单位
)
Select A.Class,A.FromUOM,FromU.Name as '从单位',A.ToUOM,ToU.Name as '到单位',A.Ratio 
Into #UOMRatio
From UOMTree as A
Inner Join Base_UOM FromU on A.FromUOM=FromU.ID
Inner Join Base_UOM ToU on A.ToUOM=ToU.ID
Order By Class,FromUOM--更新#ItemUOM 信息
Update A Set 
    A.FromBUOM=B.ToUOM,
    A.FromRatio=B.Ratio,
    A.ToBUOM=C.ToUOM,
    A.ToRatio=C.Ratio
From #ItemUOM as A
    Inner Join #UOMRatio as B on A.FromUOM=B.FromUOM
    Inner Join #UOMRatio as C on A.ToUOM=C.FromUOM--获取单位组外单位转换率
Update #ItemUOM Set BRatio=1 Where FromBUOM=ToBUOMUpdate A Set A.BRatio=B.Ratio
From #ItemUOM as A
Inner Join Base_UOMRatio as B on A.FromBUOM=B.FromUOM and A.ToBUOM=B.ToUOMUpdate A Set A.BRatio=1/B.Ratio
From #ItemUOM as A
Inner Join Base_UOMRatio as B on A.FromBUOM=B.ToUOM and A.ToBUOM=B.FromUOM 
Where ISNULL(A.BRatio,0)=0--更新单位转换率
Update #ItemUOM Set Ratio=(FromRatio/ToRatio)*BRatioUpdate A Set A.转换后=A.转换前*B.Ratio From #DemoData  as A
Inner Join #ItemUOM as B on A.UOM=B.FromUOMSelect * from #DemoData