主页 / dba / 问题 / 53025
Accepted
Dhaval
Asked: 2013-11-09 06:55:17 +0800 CST

SQL Server 中的公制到标准单位的转换

  • 772

我给出了我的两个表的简化版本，如下所示。

tbl记录 

ID  unit  val
--- ---   ---
1   KV    3
1   BAR   4
2   KV    7
2   KG    5
3   C     37

tblUnit 

unit    convUnit    formula
---     ---         ---
KV      CV          @val * .865
KG      lbs         @val / 2.205
BAR     PSI         @val / 14.504
C       F           @val * 9/5 + 32

我将无法更改表结构。

我知道哪个单位将转换为哪个单位（即我知道 KV 将转换为 CV，KG 转换为 lbs 等）。

请注意，公式是一varchar列，我从表中引用tblRecords.unit到tblUnit.unit

但我的问题是如何在单个查询中即时计算公式。即我想要以下输出。

ID  unit    val     convVal(column will be counted on the fly using formula stored in tblUnit.formula field)
--- ---     ---     ---
1   KV      3       2.595
1   BAR     4       0.27578599
2   KV      7       6.055
2   KG      5       2.267573696
3   C       37      98.6
sql-server sql-server-2008

2 个回答

  1. Best Answer

    在 SQL Server 中没有直接的方法来实现这一点，因为不允许在函数中使用动态 SQL。但是，由于您的 tblUnit 很可能很少更改，因此您可以采用间接方式：

    我们需要的是一个可以根据表格中的公式处理转换的函数。如果我们可以在函数中对这些公式进行硬编码，并在每次表格更改时以某种方式更新函数，那么问题就会得到解决。那么，我们可以吗？

    是的，我们可以：SQL Fiddle

    MS SQL Server 2008 架构设置：

    CREATE TABLE dbo.tblRecords
    ([ID] int, [unit] varchar(3), [val] DECIMAL(10,4))
;

INSERT INTO dbo.tblRecords
    ([ID], [unit], [val])
VALUES
    (1, 'KV', 3),
    (1, 'BAR', 4),
    (2, 'KV', 7),
    (2, 'KG', 5),
    (3, 'C', 37)
;


CREATE TABLE dbo.tblUnit
    ([unit] varchar(3), [convUnit] varchar(3), [formula] varchar(15))
;
GO
CREATE FUNCTION dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))
RETURNS TABLE
AS
RETURN
 SELECT @val convVal;
GO
CREATE TRIGGER updateFnConvert ON dbo.tblUnit AFTER INSERT,UPDATE,DELETE
AS
BEGIN
  DECLARE @cmd NVARCHAR(MAX);
  SELECT @cmd = 'ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))'+
  ' RETURNS TABLE AS'+
  ' RETURN SELECT CASE @unit'+
  (SELECT ' WHEN '''+unit+''' THEN '+formula
     FROM dbo.tblUnit
      FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') +
  ' END convVal;'
  EXEC(@cmd);
END




GO

    这将创建两个表，一个 fnConvert 函数存根和一个在 tblUnit 表上的触发器。

    当我们现在像这样使用 INSERT、UPDATE 或 DELETE 更改表的内容时：

    查询 1：

    INSERT INTO dbo.tblUnit
    ([unit], [convUnit], [formula])
VALUES
    ('KV', 'CV', '@val * .865'),
    ('KG', 'lbs', '@val / 2.205'),
    ('BAR', 'PSI', '@val / 14.504'),
    ('C', 'F', '@val * 9/5 + 32')
;

    触发器触发并生成如下动态 sql 语句：

    查询 2：

    SELECT 'ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))'+
' RETURNS TABLE AS'+
' RETURN SELECT CASE @unit'+
(SELECT ' WHEN '''+unit+''' THEN '+formula
   FROM dbo.tblUnit
    FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') +
' END val;'

    结果：

    |                                                                                                                                                                                                                                 COLUMN_0 |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4)) RETURNS TABLE AS RETURN SELECT CASE @unit WHEN 'KV' THEN @val * .865 WHEN 'KG' THEN @val / 2.205 WHEN 'BAR' THEN @val / 14.504 WHEN 'C' THEN @val * 9/5 + 32 END val; |

    然后执行该语句。该语句将函数替换为包含所有当前公式的版本。

    现在我们可以在任何查询中使用该函数，如下所示：

    查询 3：

    SELECT *
  FROM dbo.tblRecords AS TR
  JOIN dbo.tblUnit AS TU
    ON TR.unit = TU.unit
  CROSS APPLY dbo.fnConvert(TU.unit,TR.val) AS FC

    结果：

    | ID | UNIT | VAL | CONVUNIT |         FORMULA |     CONVVAL |
|----|------|-----|----------|-----------------|-------------|
|  1 |   KV |   3 |       CV |     @val * .865 |       2.595 |
|  1 |  BAR |   4 |      PSI |   @val / 14.504 |  0.27578599 |
|  2 |   KV |   7 |       CV |     @val * .865 |       6.055 |
|  2 |   KG |   5 |      lbs |    @val / 2.205 | 2.267573696 |
|  3 |    C |  37 |        F | @val * 9/5 + 32 |        98.6 |
    • 4

  2. 寻找这样的转换公式似乎真的很糟糕。我会使用函数或存储过程来执行此操作并将逻辑放在那里。可以这样写一个简单的SELECT，但这确实取决于您必须转换多少个案例。

    SELECT id, unit, val, 
    case when unit = 'KV'  then val * 0.865 
         when unit = 'BAR' then val / 14.504 
         when unit = 'KG'  then val / 2.205 
         when unit = 'C'   then val * 9/5 + 32
         end as conval
FROM tblRecords
    • 0

相关问题