带负号的sqlserver sum

Question

我使用sqlserver 2012。我有一个这样的查询

SELECT SUM(TH.CLEAVE_EARN_DAY), SUM(TH.CLEAVE_DAY), 
       SUM(TH.CLEAVE_EARN_DAY) - SUM(TH.CLEAVE_DAY) 
FROM TH_LEAVE_CARD TH 

结果是0，14.5，-15，所以-15是错误的。必须为-14.5

有什么建议吗？

Answer 1

这就是你可以尝试的

SELECT SUM(TH.CLEAVE_EARN_DAY), SUM(TH.CLEAVE_DAY), 
   SUM(TH.CLEAVE_EARN_DAY)*1.0 - SUM(TH.CLEAVE_DAY) 
FROM TH_LEAVE_CARD TH 

乘以1.0将只返回十进制值，减去将得到您所要求的值

Answer 2

尝试将所有参数转换为相同的数据类型，然后进行计算：

SELECT
  SUM(CAST(TH.CLEAVE_EARN_DAY AS DECIMAL(18,2))),
  SUM(CAST(TH.CLEAVE_DAY AS DECIMAL(18,2))), 
  SUM(CAST(TH.CLEAVE_EARN_DAY AS DECIMAL(18,2)) 
    - CAST(TH.CLEAVE_DAY AS DECIMAL(18,2))) AS substraction 
FROM TH_LEAVE_CARD TH 

您还可以组合：

SUM(TH.CLEAVE_EARN_DAY) - SUM(TH.CLEAVE_DAY) 

至(如果两列均为NOT NULL)：

SUM(TH.CLEAVE_EARN_DAY - TH.CLEAVE_DAY)

或者(感谢Arvo指出)：

SUM(ISNULL(TH.CLEAVE_EARN_DAY,0) - ISNULL(TH.CLEAVE_DAY,0))

Answer 3

要对列执行数学运算，请执行以下操作：

1. Used列应转换为相同的数字/小数数据类型。
2. 若要处理空值，可以使用ISNULL函数。例如：

从TH_LEAVE_CARD中选择SUM(TH.CLEAVE_EARN_DAY)、SUM(TH.CLEAVE_DAY)、SUM(cast (TH.CLEAVE_EARN_DAY) as decimal(5,1)) - SUM(cast ( (TH.CLEAVE_DAY) as decimal(5，1)