SQL自定义单位转换

Question

我正在寻找一个解决方案的自定义单位转换在SQL中，我的公司使用的数据库是Microsoft SQL服务器，我需要写一个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

我认为递归表可以找到从你想要的from unit到你想要的to unit的路径，这样的效果最好。如下所示(假设如果有路径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

Answer 2

我会使用转换表，并输入所有的组合。因此，即使是5000g的->5kg -> 1盒，我也会将gram ->盒转换为。如下所示：

create table unit_unit_conv(
   from_unit varchar(10)   not null
  ,to_unit   varchar(10)   not null
  ,rate      decimal(10,6) not null
  ,primary key(from_unit, to_unit)
);

insert into unit_unit_conv values('kilogram', 'kilogram',   1);
insert into unit_unit_conv values('kilogram', 'gram',       1000);
insert into unit_unit_conv values('kilogram', 'box',        0.2);
insert into unit_unit_conv values('gram',     'gram',       1);
insert into unit_unit_conv values('gram',     'kilogram',   0.001);
insert into unit_unit_conv values('gram',     'box',        0.0002);
insert into unit_unit_conv values('box',      'box',        1);
insert into unit_unit_conv values('box',      'kilogram',   5);
insert into unit_unit_conv values('box',      'gram',       5000);

因此，无论您有什么度量单位，您都可以将其转换为任何单位，方法是将您拥有的数量与此表中的rate列相乘。因此，如果您有一个包含以下项目的表：

create table items(
   item_id        varchar(10) not null
  ,item_qty       int not null
  ,item_qty_unit  varchar(10)
);

insert into items values('chicken', 5,    'kilogram');
insert into items values('babies',  5000, 'gram');
insert into items values('beef',    1,    'box');

...and如果您想要将所有内容转换为方框，则可以像这样查询数据：

select i.item_id
      ,i.item_qty    as qty_original
      ,item_qty_unit as qty_unit_original
      ,i.item_qty * c.rate as box_qty
  from items          i
  join unit_unit_conv c on(i.item_qty_unit = c.from_unit)
 where c.to_unit = 'box';

+---------+--------------+-------------------+----------+
| item_id | qty_original | qty_unit_original | box_qty  |
+---------+--------------+-------------------+----------+
| chicken |            5 | kilogram          | 1.000000 |
| babies  |         5000 | gram              | 1.000000 |
| beef    |            1 | box               | 1.000000 |
+---------+--------------+-------------------+----------+

Answer 3

以下解决方案在SQL server 2012上进行了测试。为了减少页面上的代码大小，我只提供质量测量，因为这些都是经过测试和工作的。

CREATE TABLE [Measurement type]
(
   [Type ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
   [Type Name] NVARCHAR(30) NOT NULL
)

CREATE TABLE [Measurement unit]
(
   [Unit ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
   [Type ID] INT REFERENCES [Measurement type]([Type ID]) NOT NULL,
   [Unit name] NVARCHAR(30) NOT NULL,
   [Unit symbol] NVARCHAR(10) NOT NULL
)

/* Use both multiplier and divizor to reduce rounding errors */
CREATE TABLE [Measurement conversions]
(
   [Type ID] INT NOT NULL REFERENCES [Measurement type]([Type ID]),
   [From Unit ID] INT NOT NULL REFERENCES [Measurement unit]([Unit ID]),
   [To Unit ID] INT NOT NULL REFERENCES [Measurement unit]([Unit ID]),
   [From Unit Offset] FLOAT NOT NULL DEFAULT(0),
   [Multiplier] FLOAT NOT NULL DEFAULT(1),
   [Divizor] FLOAT NOT NULL DEFAULT(1),
   [To Unit Offset] FLOAT NOT NULL DEFAULT(0),
   PRIMARY KEY ([Type ID], [From Unit ID], [To Unit ID])
)

INSERT INTO [Measurement type]([Type ID], [Type Name]) VALUES(4, 'Mass')

INSERT INTO [Measurement unit]([Unit ID], [Type ID], [Unit name], [Unit symbol])
VALUES (28, 4, 'Milligram', 'mg'), (29, 4, 'Gram', 'g'),
       (30, 4, 'Kilogram', 'kg'), (31, 4, 'Tonne', 't'),
       (32, 4, 'Ounce', 'oz'), (33, 4, 'Pound', 'lb'),
       (34, 4, 'Stone', 's'), (35, 4, 'hundred weight', 'cwt'),
       (36, 4, 'UK long ton', 'ton')

INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [Multiplier], [Divizor])
VALUES (4, 28, 29, 1, 1000), (4, 28, 30, 1, 1000000), (4, 28, 31, 1, 1000000000),
       (4, 28, 32, 1, 28350), (4, 32, 33, 1, 16), (4, 32, 34, 1, 224),
       (4, 32, 35, 1, 50802345), (4, 32, 36, 1, 35840)

INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [From Unit Offset], [Multiplier], [Divizor], [To Unit Offset])
SELECT DISTINCT [Measurement Conversions].[Type ID],
                [Measurement Conversions].[To Unit ID],
                [Measurement Conversions].[From Unit ID],
                -[Measurement Conversions].[To Unit Offset],
                [Measurement Conversions].[Divizor],
                [Measurement Conversions].[Multiplier],
                -[Measurement Conversions].[From Unit Offset]
FROM [Measurement Conversions]
-- LEFT JOIN Used to assure that we dont try to insert already existing keys.
LEFT JOIN [Measurement conversions] AS [Existing]
ON [Measurement Conversions].[From Unit ID] = [Existing].[To Unit ID] AND [Measurement Conversions].[To Unit ID] = [Existing].[From Unit ID]
WHERE [Existing].[Type ID] IS NULL

运行以下查询，直到它影响0行。

INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [From Unit Offset], [Multiplier], [Divizor], [To Unit Offset])
SELECT DISTINCT [From].[Type ID],
                [From].[To Unit ID] AS [From Unit ID],
                [To].[To Unit ID],
                -[From].[To Unit Offset] + (([To].[From Unit Offset]) * [From].[Multiplier] / [From].Divizor) AS [From Unit Offset],
                [From].[Divizor] * [To].[Multiplier] AS Multiplier,
                [From].[Multiplier] * [To].[Divizor] AS Divizor,
                [To].[To Unit Offset] - (([From].[From Unit Offset]) * [To].[Multiplier] / [To].Divizor) AS [To Unit Offset]
FROM [Measurement conversions] AS [From]
CROSS JOIN [Measurement conversions] AS [To]
-- LEFT JOIN Used to assure that we dont try to insert already existing keys.
LEFT JOIN [Measurement conversions] AS [Existing]
ON [From].[To Unit ID] = [Existing].[From Unit ID] AND [To].[To Unit ID] = [Existing].[To Unit ID]
WHERE [Existing].[Type ID] IS NULL
      AND [From].[Type ID] = [To].[Type ID]
      AND [From].[To Unit ID] <> [To].[To Unit ID]
      AND [From].[From Unit ID] = [To].[From Unit ID]

最后，要重置相互抵消的乘数和除数：

UPDATE [Measurement conversions] SET [Multiplicand] = 1, [Dividend] = 1 WHERE [Multiplicand] = [Dividend]