这个 SQL Server 查询划分计算有什么问题? [英] What is wrong with this SQL Server query division calculation?

问题描述

我在 SQL Server 2008 R2 数据库上有这个表结构:

I have this table structure on a SQL Server 2008 R2 database:

  CREATE TABLE FormTest
(   
clientid char(10),
DateSelected date,
A int,
B int,
C int
)

我在表格 FormTest 中填写了以下信息

I filled the table FormTest with the following information

clientid        DateSelected      A       B     C
x1              2006-06-09     65150    4921    1
x2              2006-05-05     155926   69092   1
x3              2006-01-20     95603    156892  1
x4              2006-01-20     30704    164741  1
x4              2006-02-03     65150    174834  1
x5              2006-04-28     59629    4921    1
x6              2006-01-27     30704    162356  1
x7              2006-06-30     65150    4921    1
x8              2006-07-10     65150    4921    1

最后，我运行这个 sql 查询:

And finally, I run this sql query:

SELECT clientid, (((a+ b + c) / 3) / 216647 * 10) AS Formula1 
    From FormTest

但后来我得到了这些结果:

But then I got these results:

clientid        Formula1      
x1              0
x2              0
x3              0
x4              0
x4              0
x5              0
x6              0
x7              0
x8              0

谁能告诉我我做错了什么?

Can anybody tell me what am I doing wrong?

推荐答案

这是因为你在做整数除法.您应该使用以下内容将操作数之一转换为浮点数或十进制数(取决于您正在执行的计算的精度和目的):

It's because you are doing integer division. You should convert one of the operands to float, or decimal (depending on the precision and purpose of the calculation you are doing), using something like:

((CAST((a+ b + c) AS FLOAT) / 3) / 216647 * 10)

或者可能:

(((a+ b + c) / 3.0) / 216647.0 * 10)

这篇关于这个 SQL Server 查询划分计算有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！