在 SQL 中舍入平均值时如何检索小数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13237477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I retrieve decimals when rounding an average in SQL
提问by Skullomania
Here is my SQL statement
这是我的 SQL 语句
SELECT ROUND(AVG(column_name), 100)
FROM [database].[dbo].[table]
The answer I retrieve is 3with a possible range of numbers between 1 - 5in 908 rows.
我检索到的答案是908 行3之间可能的数字范围1 - 5。
I need it to say 3.0or 3.09. Is this possible?
我需要它说3.0或3.09。这可能吗？
回答by Guffa
The average will have the same data type as the values, so cast the values:
平均值将具有与值相同的数据类型，因此转换值：
SELECT ROUND(AVG(CAST(column_name AS FLOAT)), 2) FROM [database].[dbo].[table]
回答by Skullomania
Im not sure if this will help you skullomania but try this
我不确定这是否会帮助你颅骨躁狂症，但试试这个
SELECT CAST(AVG(CAST(column_name as DECIMAL(10,2))) AS DECIMAL(10,2))FROM [table_name]
The float is an approximate, The decimal on the other hand will make it more of an exact, you may want to read up on floats math and decimals. I struggled at first with this and still feel lost at times. Hopefully this will help you out.
浮点数是一个近似值，另一方面，小数将使它更精确，您可能需要阅读浮点数数学和小数。一开始我很挣扎，但有时仍然感到迷茫。希望这会帮助你。
IN C#
not sure if I can get in trouble for posting the answer here but have you tried converting it to a double and using a ExecuteScalar()
在 C# 中不确定我是否会因为在此处发布答案而遇到麻烦，但是您是否尝试将其转换为 double 并使用 ExecuteScalar()
try this:
尝试这个：
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection conn= sqlconnectionstring;
SqlCommand cmd = new SQLCommand("SELECT STATEMENT",conn);
cmd.CommandType = CommandType.Text;
conn.Open();
Double result = Convert.ToDouble(cmd.ExecuteScalar());
lblresult.text = "the result is " + result;
conn.Dispose();
cmd.Dispose();
}
回答by Alexander
I'm not entirely sure of what you're asking, but I think that you're wanting to have the number returned with additional precision.
我不完全确定您要问什么，但我认为您希望以更高的精度返回数字。
You're wrapping the call in the ROUNDfunction, but if your column's data type does not have any precision greater then 0 (e.g. int), you will need to CAST or CONVERT the data to another type first.
您将调用包装在ROUND函数中，但如果您的列的数据类型没有任何大于 0 的精度（例如 int），您将需要先将数据 CAST 或 CONVERT 转换为另一种类型。
Here are some examples that illustrate several ways to CASTdata to different types:
下面是一些例子，说明了几种方法来CAST的数据不同的类型：
SELECT CAST(100 AS decimal(5, 2)) -- Gives you "100.00"
,CAST(100 AS float) --<== Gives you "100"
,CAST(100.0 AS float) --<== Gives you "100"
,CAST(100.01 AS float) --<== Gives you "100.01"
Also, check out MSDN: Data Types (Transact-SQL).
另外，请查看MSDN：数据类型 (Transact-SQL)。
回答by Lamak
You don't even need to do a ROUNDthere, really. You could just convert your values to DECIMAL, like this:
你甚至不需要在ROUND那里做，真的。您可以将您的值转换为DECIMAL，如下所示：
SELECT AVG(CONVERT(DECIMAL(16,2),YourColumn))
FROM YourTable
回答by Vikas Sharma
CAST(AVG(column_name) AS DECIMAL(5, 2))
It's show 100 as 100.00 and 100.05 as 100.05
它显示 100 为 100.00 和 100.05 为 100.05
