如何使用T-Sql使用select将度量单位从1级转换为其他级别

Question

我有一个表保持项目的测量单位，它的数量看起来像这样

| ItemID |  Qty  |  UOM  |  Factor  | Level |
+--------+-------+-------+----------+-------+
|   9    |   0   |   ctn |    48    |   1   |
|   9    |   1   |   Can |    380   |   2   |
|   9    |   0   |   ML  |    1     |   3   |

这意味着物料编号9个单位是

1ctn = 48can

1can = 380ML

且此项目还有1罐(数量)

我想通过使用T-Sql select脚本将1can转换为ctn或ML。

请帮帮我！谢谢!

Answer 1

如果您有未知数量的级别，则可以使用递归公用表表达式遍历所有可能的级别：

WITH CTE AS
(   SELECT  t1.ItemID, 
            UnitFrom = t1.UOM, 
            UnitTo = t2.UOM, 
            Factor = CAST(t2.Factor AS DECIMAL(15, 2)), 
            NextLevelFactor = t1.Factor,
            t1.Level
    FROM    T t1
            INNER JOIN T t2
                ON t2.ItemID = t1.ItemID
                AND t2.Level + 1 = t1.Level
    UNION ALL
    SELECT  t1.ItemID, 
            UnitFrom = t2.UOM, 
            UnitTo = t1.UnitTo, 
            Factor = CAST(t1.Factor * t1.NextLevelFactor AS DECIMAL(15, 2)),
            NextLevelFactor = t2.Factor,
            t2.Level
    FROM    CTE t1
            INNER JOIN T t2
                ON t2.ItemID = t1.ItemID
                AND t2.Level = t1.Level + 1
)
SELECT  ItemID,
        UnitFrom,
        UnitTo,
        Factor
FROM    CTE;

其结果是一个转换表：

ItemID  UnitFrom    UnitTo  Factor
9       Can         ctn     48
9       ML          Can     380
9       ML          ctn     18240

然后，您可以连接到主表，以获得所有级别之间的转换系数。

Answer 2

试试这个：

select 
    t1.itemid, t1.uom, t1.level,
    isnull(nullif(1.0*t2.qty,0)/t1.factor,0) as  qty_calc
from table t1
join table t2
    on  t1.itemid=t2.itemid
    and t1.Level+1=t2.Level

在本例中，这是用于计算上级(ctn)的。对于较低的级别(ml)，您可以将join更改为t1.Level=t2.Level+1，并将计算操作符更改为乘法而不是除法。