基于文本列的SQL数学计算

I have a table with 3 text columns on which I want to do some mathematical calculations. Table looks like below:

我有一个包含3个文本列的表,我想在其上进行一些数学计算。表如下所示:

Date        Column1    Column2    Column3
-----------------------------------------
2012-08-01  STABLE     NEG        STABLE
2012-08-02  NEG        NEG        STABLE
2012-08-03  STABLE     STABLE     STABLE

Want I want to achieve is

我希望实现的是

If 2/3 columns is equal to 'STABLE' then it returns 66% i.e. 2/3, as it is in the first row

如果2/3列等于'STABLE',那么它返回66%,即2/3,因为它在第一行中

If 1/3 columns is equal to 'STABLE' then it returns 33% i.e. 1/3, as it is in the second row

如果1/3列等于'STABLE',那么它返回33%,即1/3,因为它在第二行

If 3/3 columns is equal to 'STABLE' then it returns 100% i.e. 3/3, as it is in the third row

如果3/3列等于'STABLE',那么它返回100%,即3/3,因为它在第三行

I want to know how I can achieve this using SQL? I'm currently working on MSSQL Server 2008 R2.

我想知道如何使用SQL实现这一目标?我目前正在研究MSSQL Server 2008 R2。

I hope my question is clear enough.

我希望我的问题很清楚。

2 个解决方案

#1

So, something like this?

那么,这样的事情呢?

select 
   ((case when col1 = 'Stable' then 1.0 else 0.0 end) + 
   (case when col2 = 'Stable' then 1.0 else 0.0 end) + 
   (case when col3 = 'Stable' then 1.0 else 0.0 end)) / 3.0
from yourtable

You can do some formatting on the output, but should be very close to what your looking for.

你可以在输出上做一些格式化,但应该非常接近你想要的。

#2

If those are the only choices for column values:

如果这些是列值的唯一选择:

select ( Len( Column1 ) + Len( Column2 ) + Len( Column3 ) - 9 ) / 3 / 3.0 * 100.0 as 'Percent'
  from Foo

The optimizer should handle the constant folding. They are shown for clarity.

优化器应该处理常量折叠。为清楚起见,显示了它们。

Divine Comedy describes a place for people who write code like this.

Divine Comedy为编写这样代码的人描述了一个地方。

#1