如何在分区更新后显示小数 [英] How can I show decimals after division update

问题描述

我有一张桌子，我希望通过将一个字段划分为

另一个字段来更新。更新运行没有错误，但结果显示为

只有正整数。


结果字段的数据类型为float。

其他字段的数据类型是int。


我试过将7除以10000进行测试，这应该给我

0.0007 。数据库中的结果为0.


如何定义一个字段以显示带小数位的完整值？


如果我输入.0007通过企业管理器进入现场，显示

0007.当我通过划分更新时，它显示0.


欢迎任何帮助。


Joel

***通过Developersdex发送 http://www.developersdex.com ***

不要只是参加USENET ......获得奖励！

解决方案

当您将整数除以整数时，结果也将是一个

整数：


SELECT 23/20 - 结果1

当您将整数除以小数类型时，结果将为十进制：


SELECT 23 / 20.0 - 结果1.1500

上述行为称为数据类型优先级。请参阅SQL 2000 Books

Online< tsqlref.chm :: / ts_da-db_2js5.htm>更多信息。


另外，请注意float和real是近似数据类型。一些十进制的

值无法表示，所以你最终会得到收盘，但并不总是确切地说，这些都是结果。如果你需要存储确切的值，请使用小数。


SELECT CAST（23 / 20.0 AS浮动） - 结果1.1499999999999999

br />
希望这会有所帮助。


Dan Guzman

SQL Server MVP


"乔尔" <一个******* @ devdex.com>在消息中写道

news：40 ********************* @ news.frii.net ...

我有一张桌子，我希望通过将一个字段划分为另一个字段来更新。更新运行没有错误，但结果只是一个正整数。

结果字段的数据类型是float。
其他字段的数据类型是int。

我试过将7除以10000，这应该给我
0.0007。数据库中的结果是0.

如何定义一个字段以显示带小数位的完整值？

如果我通过企业输入.0007到现场经理，它显示
0007.当我通过划分更新时，它显示0.

欢迎任何帮助。

Joel

***通过开发人员发送的指南 http://www.developersdex.com *** <不要只参加USENET ......获得奖励！

感谢您的回复。


问题是我将小数（实际上是浮点类型）除以

十进制（浮点类型）并且仍然只得到整数结果。


我尝试从链接到

的Access数据库完全相同的计算，得到了正确的结果。


当我尝试在企业管理器或SQL查询分析器中执行此操作时，我只有
得到整数结果。由于大多数计算都小于零，

我得零。


我试过在企业管理器中进行计算，其中

结果是一个整数，我得到了正确的结果。


我错误地认为如果我将300除以650我应该得到0.4615

等人？我想我应该这样做，但是当我在SQL中进行

计算时这不会发生，但是当我使用
进行计算时确实会发生这种情况。
Access链接到SQL数据库。


为什么????


***通过Developersdex发送 http://www.developersdex.com ***

不要只是参加USENET ...奖励它！

Dan，


我刚刚尝试了你的演员陈述并且它奏效了。然后我试了一下

颠倒了20 / 23.0并且它有效。


但是，如果我将.0作为声明的一部分而遗漏，它会给我0.

也就是说，如果我除以20/23，我得到0.如果我为。
值20.0 / 23中的任何一个输入.0，我得到.869565 。


即使我不使用CAST语句也行。


我表中的值都没有小数位。我有
将列数据类型定义为float。我怎样才能获得分数

来为这些领域工作？看来我需要将小数（。）

作为获得小数结果的其中一个值的一部分。


谢谢


Joel


***通过Developersdex发送 http://www.developersdex.com ***

不要只是参加USENET ......获得奖励！

I have a table which I want to update by dividing one field into
another. The update runs with no errors, but the results come out as
only a positive integer number.

The datatype for the result field is float.
The datatype for the other fields are int.

I have tried testing by dividing 7 by 10000, which should give me
0.0007. The result in the database is 0.

How do I define a field to show the full value with decimal positions?

If I enter .0007 into the field through the Enterprise Manager, it shows
0007. When I update by dividing, it shows 0.

Any help would be welcome.

Joel
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!

解决方案

When you divide an integer by an integer, the result will also be an
integer:

SELECT 23/20 --result 1

When you divide an integer by an decimal type, the result will be decimal:

SELECT 23/20.0 --result 1.1500

The above behavior is known as data type precedence. See the SQL 2000 Books
Online <tsqlref.chm::/ts_da-db_2js5.htm> for more information.

Also, note that float and real are approximate data types. Some decimal
values cannot be represented so you''ll end up with close, but not always
exact, results with these. Use decimal if you need to store exact values.

SELECT CAST(23/20.0 AS float) --result 1.1499999999999999

--
Hope this helps.

Dan Guzman
SQL Server MVP

"joel" <an*******@devdex.com> wrote in message
news:40*********************@news.frii.net...

I have a table which I want to update by dividing one field into
another. The update runs with no errors, but the results come out as
only a positive integer number.

The datatype for the result field is float.
The datatype for the other fields are int.

I have tried testing by dividing 7 by 10000, which should give me
0.0007. The result in the database is 0.

How do I define a field to show the full value with decimal positions?

If I enter .0007 into the field through the Enterprise Manager, it shows
0007. When I update by dividing, it shows 0.

Any help would be welcome.

Joel
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!

Thanks for your response.

The problem is that I am dividing a decimal(actually a float type) by a
decimal(float type) and still getting only integer results.

I tried exactly the same calculation from an Access database linked to
the same SQL database and got the correct result.

When I try doing it in Enterprise Manager or SQL Query Analyzer, I only
get integer results. Since most of the calculations are less than zero,
I get zero.

I have tried doing the calculation in Enterprise Manager where the
result is an integer, and I get the right result.

Am I wrong in believing that if I divide 300 by 650 I should get 0.4615
et al? I think I should, but this doesn''t happen when I do the
calculation in SQL, but it does happen when I do the calculation using
Access linked to the SQL database.

Why????

*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!

Dan,

I just tried your cast statement and it worked. I then tried it
reversed 20/23.0 and it worked.

However, if I leave out the .0 as part of the statement, it gives me 0.
That is, if I divide 20/23 I get 0. If I put in a .0 for any of the
values 20.0/23 I get .869565.

This works even if I don''t use the CAST statement.

Neither of the values in my table have a decimal position. I have
defined the column datatype for both as float. How can I get the divide
to work for these fields? It seems that I need to have the decimal (.)
as part of one of the values to get a decimal result.

Thanks

Joel

*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!

这篇关于如何在分区更新后显示小数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！