在SQL单元转换表(unit conversion table in SQL)

我想在一个数据库实现单位转换功能的质量，体积，温度等我的背景是在软件开发的单位，所以我自然对这个任务创造了一堆用户定义函数的倾斜。但我暗自怀疑有实现这个更“SQLish”的方式。

的基于表的方法，如所描述的此处或这里将是优异的，但我看到延伸这种方法对于不是由单个乘法器相关的单元，诸如温度（F到C，C的没有简单的方法-to-F）或原子质量（公斤·摩尔到公斤）。我所看到的多步骤方法（如描述的在这里），但这些似乎太令人费解的是有用的。

我失去了一些东西很明显这里，或者是功能真的去的唯一途径？

Answer 1:

为了处理温度转换，转换表应该有一个乘数和偏移。与F - > C，例如，偏移将是-32和乘法器5/9。

如果你事先知道所有可能的单位，然后基于表的消息，工作正常。但是，如果你想有一个完全灵活的系统，如米^ 5 *升英寸^ * 5加仑，那么你会希望有一个巴塞单位表和用户定义的函数来执行转换。该功能可能会使用递归CTE来解析单位表达。所有这一切都将是相当复杂的，所以希望你有单位的完整列表。

Answer 2:

最后，我决定坚持使用UDF的。作出这一决定的主要动机是，基于表的方法的复杂性似乎没有道理，因为这一任务的次数并不频繁。此外，一些功能（例如ConvertMass()取决于子查询（以确定给定成分的原子量，例如）。所以UDF的似乎是最谨慎的路要走。

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
)
GO
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
)
GO
/* Use both multiplicand and dividend 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),
   [Multiplicand] FLOAT NOT NULL DEFAULT(1),
   [Dividend] FLOAT NOT NULL DEFAULT(1),
   [To Unit Offset] FLOAT NOT NULL DEFAULT(0),
   PRIMARY KEY ([Type ID], [From Unit ID], [To Unit ID])
)
GO
SET IDENTITY_INSERT [Measurement type] ON
GO
INSERT INTO [Measurement type]([Type ID], [Type Name])
VALUES (1, 'Length'), (2, 'Area'), (3, 'Volume'), (4, 'Mass'), (5, 'Temperature') -- ...
GO
SET IDENTITY_INSERT [Measurement type] OFF
GO
GO
SET IDENTITY_INSERT [Measurement unit] ON
GO
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')
GO
SET IDENTITY_INSERT [Measurement unit] ON
GO
INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [Multiplicand], [Dividend])
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)
GO
INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [From Unit Offset], [Multiplicand], [Dividend], [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].[Dividend],
                [Measurement Conversions].[Multiplicand],
                -[Measurement Conversions].[From Unit Offset]
FROM [Measurement Conversions]
-- LEFT JOIN Used to reduce errors on inserting same key twice.
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
GO

然后运行下面的查询直到它影响了零行，如果有不止一条路径通往相同的价值观之间的不同的换算系数可能抱怨多项相等的ID。

INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [From Unit Offset], [Multiplicand], [Dividend], [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].[Multiplicand] / [From].Dividend) AS [From Unit Offset],
                [From].[Dividend] * [To].[Multiplicand] AS Multiplicand,
                [From].[Multiplicand] * [To].[Dividend] AS Dividend,
                [To].[To Unit Offset] - (([From].[From Unit Offset]) * [To].[Multiplicand] / [To].Dividend) AS [To Unit Offset]
FROM [Measurement conversions] AS [From]
CROSS JOIN [Measurement conversions] AS [To]
-- LEFT JOIN Used to reduce errors on inserting same key twice.
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]

Finaly，除去乘数和divizors直接取消海誓山盟出来：

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