如何计算字符串语句 [英] how to calculate a string statement

问题描述

给定一个字符串组合，它是一个计算语句，我怎样才能得到结果，在这种情况下是下面代码中的列 cal.我知道我可以使用 case 但有什么直接的方法可以进行计算吗?

Given a string combination which is a calculation statement, how can I get the result, in this case is column cal in below code. I know I can use case but is there any direct way to do the calculation?

    create table tl_test
    (
            cl1 int
    )

    create table tl_test2
    (
            cl1 char(1)
    )
    insert into tl_test values (21), (43), (13), (36), (41)
    insert into tl_test2 values ( '+'), ('-'), ('*'), ('/')

    select *,
    cast(c1 as varchar) + f1 
    + cast(c2 as varchar) + f2 
    + cast(c3 as varchar) + f3 
    + cast(c4 as varchar) + f4 
    + cast(c5 as varchar) as cal
    from(
    SELECT A.cl1 as c1, f1.cl1 as f1,  b.cl1 as c2,f2.cl1 as f2, C.cl1 as c3, f3.cl1 as f3, D.cl1 as c4, f4.cl1 as f4,  E.cl1 as c5
    FROM TL_TEST A
    CROSS JOIN TL_TEST2 f1
    CROSS JOIN TL_TEST B
    CROSS JOIN TL_TEST2 f2
    CROSS JOIN TL_TEST C
    CROSS JOIN TL_TEST2 f3
    CROSS JOIN TL_TEST D
    CROSS JOIN TL_TEST2 f4
    CROSS JOIN TL_TEST E
    )a
    WHERE c1 != c2
    and c1 != c3
    and c1 != c4
    and c1 != c5
    and c2 != c3
    and c2 != c4 
    and c2 != c5
    and c3 != c4
    and c3 != c5
    and c4 != c5

推荐答案

阅读 这个相关主题，尤其是 Erland Sommarskog 的回答.

Read this related thread, especially the answer by Erland Sommarskog.

对不起，没有办法在纯 ad-hoc T-SQL 中做到这一点.

I'm sorry, there is no way to do this in pure ad-hoc T-SQL.

1) 您可以使用动态 SQL，它有一个缺陷:您的示例将针对 INTs

1) You can use dynamic SQL, which has one flaw: Your example would be calculated for INTs

DECLARE @cmd VARCHAR(100)='SELECT (' + '21*41-36 / 13+43' /*Your formula coming from somewhere*/ + ')';
EXEC(@cmd);

结果 902

2) 你可以使用 XML 的隐式能力来计算这样的

2) You can use XMLs implicit ability to calculate like this

SELECT CAST('' AS XML).value('21*41-36 div 13+43','float') 
hint: "/" must be replaced with " div "

结果 901.23077

遗憾的是，XML 类型的.value() 知道表达式 和值.可以从表的列动态引入值(sql:column("ColumnName"))，但表达式必须是文字.

It's a pitty, that the XML type's .value() knows expressions and values. A value can be introduced from a table's column dynamically (sql:column("ColumnName")), but an expression must be a literal.

3) 并且您可能包含 .Net-code 作为程序集(CLR 函数).

3) And you might include .Net-code as assembly (CLR function).

抱歉，这并不容易...

Sorry, there is no easy going...

如果您需要精确的结果，您应该在动态创建的 SQL 中使用 XML 方法.

If you need exact results you should use the XML approach in dynamically created SQL.

DECLARE @tbl TABLE(ID INT IDENTITY,SomeFormula VARCHAR(100));
INSERT INTO @tbl(SomeFormula) VALUES
 ('1+2')
,('21*41-36/13+43')
,('(1+3)*4')
,('3.6 div (2.5-1)');

DECLARE @cmd VARCHAR(MAX)=
(
    SELECT 'DECLARE @x XML=CAST('''' AS XML);' +
    STUFF
    (
      (
        SELECT ' UNION ALL SELECT ' + CAST(ID AS VARCHAR(MAX)) + ' AS ID, @x.value(''' + REPLACE(SomeFormula,'/',' div ') + ''',''float'')'
        FROM @tbl
        FOR XML PATH('')
      ),1,11,''
    )
);
PRINT @cmd;
EXEC (@cmd);

这是生成的语句

DECLARE @x XML=CAST('' AS XML);
          SELECT 1 AS ID, @x.value('1+2','float') 
UNION ALL SELECT 2 AS ID, @x.value('21*41-36 div 13+43','float') 
UNION ALL SELECT 3 AS ID, @x.value('(1+3)*4','float') 
UNION ALL SELECT 4 AS ID, @x.value('3.6 div (2.5-1)','float')

这就是结果

ID  
1   3
2   901,23077
3   16
4   2,4

这篇关于如何计算字符串语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！