SQL自定义单位转换

Question

我正在寻找一种用于SQL中自定义单位转换的解决方案，我公司使用的数据库是Microsoft SQL
Server，因此我需要编写一个SQL以返回基于“单位转换表”的转换因子

说：

Item: chicken wings (itemid 1001)
vendor: food wholesale ltd (vendorid 5000)
unitid: gram (id=10)
unitid: kilogram (id=500)
unitid: boxes (id=305)
Quantity: 1000 grams = 1kgs = 5 boxs

单位换算表：

itemid | vendorid | unit1id | unit2id | quantity1 | quantity2

1001 5000 10 500 1000 1

1001 5000 500 305 1 5

问题：如果我有10盒，以克为单位的鸡翅的期末存货是多少？

如何编写此sql返回“转换因子”？

提前致谢

Answer 1

我认为递归表最好找到从您期望的单位到期望的单位之间的路径。这样的事情（这假设数据库中如果存在路径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 toQuantity
into #conversionTable
union
select  1001
        ,5000
        ,500
        ,305
        ,1
        ,5
union
select 1001
        ,5000
        ,305
        ,500
        ,5
        ,1
union
select  1001
        ,5000
        ,500
        ,10
        ,1
        ,1000

declare @fromUnit int
        ,@toUnit int
        ,@input int
set @fromUnit = 305 --box
set @toUnit =  10 --gram
set @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.multiplicationFactor
from
(
    select top 1 * from recursiveTable 
    where (fromUnit = @fromUnit
    and toUnit = @toUnit)
) r