我认为递归表最好找到从您期望的单位到期望的单位之间的路径。这样的事情（这假设数据库中如果存在路径a-> b-> c，也存在路径c-> b->
a。如果没有，则可以对其进行修改以搜索两个方向） 。
select 1001 as itemID ,5000 as vendorID ,10 as fromUnit ,500 as toUnit ,cast(1000 as float) as fromQuantity ,cast(1 as float) as toQuantityinto #conversionTableunionselect 1001 ,5000 ,500 ,305 ,1 ,5unionselect 1001 ,5000 ,305 ,500 ,5 ,1unionselect 1001 ,5000 ,500 ,10 ,1 ,1000declare @fromUnit int ,@toUnit int ,@input intset @fromUnit = 305 --boxset @toUnit = 10 --gramset @input = 10;with recursiveTable as( select 0 as LevelNum ,ct.fromUnit ,ct.toUnit ,ct.toQuantity / ct.fromQuantity as multiplicationFactor from #conversionTable ct where ct.fromUnit = @fromUnit union all select LevelNum + 1 ,rt.fromUnit ,ct.toUnit ,rt.multiplicationFactor * (ct.toQuantity / ct.fromQuantity) from #conversionTable ct inner join recursiveTable rt on rt.toUnit = ct.fromUnit)select @input * r.multiplicationFactorfrom( select top 1 * from recursiveTable where (fromUnit = @fromUnit and toUnit = @toUnit)) r
