防止SQL中的舍入 [英] Prevent rounding in SQL

问题描述

我的查询是将十进制值四舍五入为整数。我理解十进制（整数，小数）本身的语法，但是我不明白我是如何在查询中实现它的。我没有看到专门解决这个问题的答案，如果有的话，请简单地指向我。



我在VBA宏中运行以下查询在Excel中，所以我认为我正在使用MS SQL，如果我出错，请纠正我。



SELECT * from DateSlicedCDR.csv AS Q LEFT OUTER JOIN AreaCodes。 csv AS R ON Q. [AreaPrefix] = R. [AreaPrefix] ORDER BY [BeginTimeDate] ASC



我意识到csv文件绝不是理想的但是我想要的暂时搁置这一点并将查询理顺，然后将数据格式更改为更好更快的内容。



发生的是其中一个字段，具有可变小数位数（最多7位小数），正在四舍五入，以便当我写出从我的连接创建的表时，我不再具有正确的值。



如果我必须明确说明字段而不是*，那很好。



如果我在发布此问题时做了任何错误，或者您需要其他信息，请告诉我，因为我对该网站相对较新。



谢谢。



我的尝试：



我查看了我的查询生成的记录集中的字段，可以看到值已四舍五入到最接近的整数。我在网上搜索，发现我似乎需要实现十进制语句，但我找不到如何做的例子。

My query is rounding decimal values to integer. I understand the syntax of decimal(integers,decimals) itself however I do not understand how I implement this in my query. I do not see an answer which specifically addresses this so, if there is one, please simply point me to it.

I am running the following query within a VBA macro within Excel so I would think I am using MS SQL, please correct me if I am in error.

SELECT * from DateSlicedCDR.csv AS Q LEFT OUTER JOIN AreaCodes.csv AS R ON Q.[AreaPrefix] = R.[AreaPrefix] ORDER BY [BeginTimeDate] ASC

I realize csv files are by no means the ideal however I want to leave that aside for the moment and get the query straightened out then later perhaps change the data format to something better and faster.

What is happening is that one of the fields, which has a variable number of decimal places (max 7 decimals), is getting rounded so that when I write out the table created from my join, I no longer have the correct values.

If I have to explicitly state the fields instead of *, that is fine.

If I have done anything wrong in the posting of this question, or if you need additional information, please let me know as I am relatively new to the site.

Thank you.

What I have tried:

I looked at the field in the record set produced by my query and could see that the values had been rounded to the nearest integer. I searched the web and found that it appears I need to implement the decimal statement, but I can't find an example of how to do it.

推荐答案

我会建议问题与文本驱动程序有关。使用文本驱动程序读取CSV文件时，它会根据读取的第一个x行确定每列的数据类型，然后假定该列的其余数据相同。

您的问题可能正在发生，因为第一个'x'行实际上是整数数据。



最好的解决方案是创建一个Schema。 ini文件，因为它会告诉驱动程序确切的列类型是什么。

参考以下链接;

MSDN - Schema.ini文件（文本文件）驱动程序） [ ^ ]

将Schema文件放在与CSV文件相同的目录中。你会得到一个名叫鲍勃的叔叔。

编辑：一个Schema.ini可以用于多个文件



亲切的问候

I would suggest the problem is to do with the Text Driver. When a CSV file is read using the Text Driver it determines the data-type for each column based on the first 'x' number of rows it reads, then the remaining data for that column is presumed to be the same.
Your issue is possibly occurring because the first 'x' rows are actually integer data.

The best solution is to create a Schema.ini file as it will tell the driver what the exact column type is.
Refer to following link;
MSDN - Schema.ini File (Text File Driver)[^]
Place the Schema file in the same directory as the CSV file & you will get an Uncle named Bob.
One Schema.ini can be used for multiple files

Kind Regards

这篇关于防止SQL中的舍入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！