如何使用Insert Into在Sql Server中减去负小数
我正在尝试编写一个查询来跟踪两个表中十进制值的历史记录。
Table1
PK Num1 D1 D2
1 1 -0.00375 -0.005
2 1 -0.00325
Table2
PK Num1 D1 D2
1 1 -0.00375 -0.0025
2 2 -0.005
Table3（历史表）
Num1 OldD2 NewD2 D2Difference
1 -0.005 -0.0025 0.0000//This is where I am having trouble
我的查询将添加 2 个 D2 值，但不会减去。它总是返回 0.00 这是我的查询。
BEGIN TRANSACTION
INSERT INTO Table3
SELECT Table1.Num1, Table1.D2, Table2.D2, (Table1.D2 - Table2.D2)
FROM Table1
INNER JOIN Table2 ON Table1.Num1 = Table2.Num1 AND Table1.D1 = Table2.D1
COMMIT
同样，我可以加到小数点后两位，但减去总是返回 0.00。我知道我错过了森林中的树木（或者是相反？）。
注意：所有小数均为 (5,5)，连接是表 1 和表 2 的结果，每个 Num1 都有唯一的 D1 值。
I am trying to write a query that tracks the history of decimal values in 2 tables.
Table1
PK Num1 D1 D2
1 1 -0.00375 -0.005
2 1 -0.00325
Table2
PK Num1 D1 D2
1 1 -0.00375 -0.0025
2 2 -0.005
Table3 (The History Table)
Num1 OldD2 NewD2 D2Difference
1 -0.005 -0.0025 0.0000//This is where I am having trouble
My query will add the 2 D2 values but not subtract. It always returns 0.00
Here is my query.
BEGIN TRANSACTION
INSERT INTO Table3
SELECT Table1.Num1, Table1.D2, Table2.D2, (Table1.D2 - Table2.D2)
FROM Table1
INNER JOIN Table2 ON Table1.Num1 = Table2.Num1 AND Table1.D1 = Table2.D1
COMMIT
Again, I can add to the 2 decimals but subtracting always returns 0.00. I know I'm missing the trees through the forest (or is it the other way around?).
NOTE: All decimals are (5,5) and the joins are a result of tables 1 and 2 having a unique D1 value for each Num1.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于您仅选择两个表中相等的 D1 - 减法操作将返回 0.00
只需删除 JOIN 条件
AND Table1.D1 = Table2.D1编辑： 注意
问题是更改我的答案并不明显，因为初始 sql 查询已更改
Since you are selecting only D1 which are equal in both tables - substract operation will return 0.00
Just remove JOIN condition
AND Table1.D1 = Table2.D1EDIT: Note
As question was changed my answer is not obvious because initial sql query was changed