SQL 如何在查询中使用除法功能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13452422/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use the divide function in the query?
提问by Adalarasan_New
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
回答by Damien_The_Unbeliever
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 - intdivision performs truncation, so anything less than 100% would give you a result of 0:
假设所有这些列都是int，那么首先要整理出来的是将它们中的一个或多个转换为更好的数据类型——int除法执行截断，所以任何小于 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.0which 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将强制计算结果用floats 而不是ints完成的数字之一进行乘法运算。通过选择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.
我也对你的括号有点困惑 - 我想我说得对 - 但是你在单个值周围有括号，然后在其他地方你在同一级别混合了运算符（-和/），所以依赖在优先规则上定义首先应用哪个运算符。
回答by Kami
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。
