如何在查询中使用除法函数？

I want to show the percentage for the Overshipment column.

我想显示Overshipment列的百分比。

My sample query

我的示例查询

select (SPGI09_EARLY_OVER_T – (SPGI09_OVER_WK_EARLY_ADJUST_T) / (SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T  + SPGR99_ON_TIME_Q))
from 
CSPGI09_OVERSHIPMENT

Table Name - CSPGI09_OVERSHIPMENT

表名 - CSPGI09_OVERSHIPMENT

My formula:

我的公式：

-------------------------------------------------------
SPGI09_EARLY_OVER_T – (SPGI09_OVER_WK_EARLY_ADJUST_T)
-------------------------------------------------------
SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T  + SPGR99_ON_TIME_Q

and I want to show the result with %, for example, 66.57%

我想用％显示结果，例如，66.57％

I'm using SQL SERVER 2008

我正在使用SQL SERVER 2008

2 个解决方案

#1

Assuming all of these columns are int, then the first thing to sort out is converting one or more of them to a better data type - int division performs truncation, so anything less than 100% would give you a result of 0:

假设所有这些列都是int，那么要排序的第一件事是将它们中的一个或多个转换为更好的数据类型 - int division执行截断，因此任何小于100％的列都会得到0的结果：

select (100.0 * (SPGI09_EARLY_OVER_T – SPGI09_OVER_WK_EARLY_ADJUST_T)) / (SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T  + SPGR99_ON_TIME_Q)
from 
CSPGI09_OVERSHIPMENT

Here, I've mutiplied one of the numbers by 100.0 which will force the result of the calculation to be done with floats rather than ints. By choosing 100, I'm also getting it ready to be treated as a %.

在这里，我将其中一个数字乘以100.0，这将强制计算结果用浮点数而不是整数来完成。通过选择100，我也准备将其视为％。

I was also a little confused by your bracketing - I think I've got it correct - but you had brackets around single values, and then in other places you had a mix of operators (- and /) at the same level, and so were relying on the precedence rules to define which operator applied first.

我对你的包围感到有些困惑 - 我想我已经把它弄好了 - 但是你有一个括号围绕单个值，然后在其他地方你有同一级别的运算符（ - 和/）混合，所以依赖于优先规则来定义首先应用哪个运算符。

#2

Try something like this

尝试这样的事情

select Cast((SPGI09_EARLY_OVER_T – (SPGI09_OVER_WK_EARLY_ADJUST_T) / (SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T  + SPGR99_ON_TIME_Q)) as varchar(20) + '%' as percentageAmount
from CSPGI09_OVERSHIPMENT

I presume the value is a representation in percentage - if not convert it to a valid percentage total, then add the % sign and convert the column to varchar.

我假设值是百分比表示 - 如果不将其转换为有效百分比总和，则添加％符号并将列转换为varchar。

#1

select (100.0 * (SPGI09_EARLY_OVER_T – SPGI09_OVER_WK_EARLY_ADJUST_T)) / (SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T  + SPGR99_ON_TIME_Q)
from 
CSPGI09_OVERSHIPMENT