T-SQL 平均值四舍五入到最接近的整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7063962/
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
T-SQL average rounded to the closest integer
提问by user441365
I'm not sure if this has been asked before, but how do I get the average rounded to the closest integer in T-SQL?
我不确定之前是否有人问过这个问题，但是如何将平均值四舍五入到 T-SQL 中最接近的整数？
采纳答案by user441365
This worked for it:
这对它有用：
CONVERT(int,ROUND(AVG(CAST(COLUMN-NAME AS DECIMAL)) ,0))
Isn't there a shorter way of doing it though?
难道没有更短的方法吗？
回答by David Steele
This should do it. You might need a GROUP BY on the End depending on what you are looking for the average of.
这应该这样做。根据您要查找的平均值，您可能需要在最后使用 GROUP BY。
SELECT CONVERT(int,ROUND(AVG(ColumnName),0))
FROM
TableName
EDIT: This question is more interesting than I first thought.
编辑：这个问题比我最初想象的更有趣。
If we set up a dummy table like so...
如果我们像这样设置一个虚拟表......
WITH CTE
AS
(
SELECT 3 AS Rating
UNION SELECT 4
UNION SELECT 7
)
SELECT AVG(Rating)
FROM
CTE
We get an integer average of 4
我们得到整数平均值 4
However if we do this
但是，如果我们这样做
WITH CTE
AS
(
SELECT 3.0 AS Rating
UNION SELECT 4.0
UNION SELECT 7.0
)
SELECT AVG(Rating)
FROM
CTE
We get a decimal average of 4.666..etc
我们得到 4.666 的小数平均值......等
So it looks like the way to go is
所以看起来要走的路是
WITH CTE
AS
(
SELECT 3 AS Rating
UNION SELECT 4
UNION SELECT 7
)
SELECT CONVERT(int,ROUND(AVG(CONVERT(decimal,Rating)),0))
FROM CTE
Which will return an integer value of 5 which is what you are looking for.
这将返回一个整数值 5，这就是您要查找的值。
回答by user1330271
If you are in SQL Server, just use round(avg(column * 1.0), 0).
如果您在 SQL Server 中，只需使用round(avg(column * 1.0), 0).
The reason for * 1.0is because sql server in some cases returns calculations using the same datatype of the values used in the calculation. So, if you calculate the average of 3, 4 and 4, the result is 3.66..., but the datatype of the result is integer, therefore the sql server will truncate 3.66... to 3, using * 1.0implicit convert the input to a decimal.
原因* 1.0是因为 sql server 在某些情况下使用与计算中使用的值相同的数据类型返回计算。所以，如果你计算3、4和4的平均值，结果是3.66...，但结果的数据类型是整数，因此sql server会将3.66...截断为3，使用* 1.0隐式将输入转换为一个小数。
Alternatively, you can convert or cast the values before the average calculation, like cast(column as decimal)instead of using the * 1.0trick.
或者，您可以在计算平均值之前转换或转换值，cast(column as decimal)而不是使用* 1.0技巧。
If your column it's not a integer column, you can remove the * 1.0.
如果您的列不是整数列，则可以删除* 1.0.
PS: the result of round(avg(column * 1.0), 0)still is a decimal, you can explicit convert it using convert(int, round(avg(column * 1.0), 0), 0)or just let whatever language you are using do the job (it's a implicit conversion)
PS：round(avg(column * 1.0), 0)仍然是十进制的结果，您可以使用显式转换它，convert(int, round(avg(column * 1.0), 0), 0)或者让您使用的任何语言完成这项工作（这是一种隐式转换）
回答by tim franklin
Select cast(AVG(columnname) as integer)
回答by t-clausen.dk
select cast(avg(a+.5) as int) from
(select 1 a union all select 2) b
If you don't like shortcuts, you could use the long way:
如果你不喜欢捷径，你可以使用长路：
select round(avg(cast(a as real)), 0)
from (select 1 a union all select 2) b
回答by XGIS
T-SQL2018.
T-SQL2018。
CAST(ROUND(COLUMN, 0) AS INT)This code does the job for me and gives the output I require so a 4.8 becomes 5.
CAST(ROUND(COLUMN, 0) AS INT)这段代码为我完成了这项工作，并提供了我需要的输出，因此 4.8 变为 5。
whereas
然而
CAST(AVG(COLUMN) AS INT)This code almost does the job but rounds down, so 4.8 becomes a 4 and not 5.
CAST(AVG(COLUMN) AS INT)这段代码几乎可以完成这项工作，但四舍五入，因此 4.8 变成了 4 而不是 5。
回答by Gerardo Lima
The following statements are equivalent:
以下语句是等效的：
-- the original code
CONVERT(int, ROUND(AVG(CAST(mycolumn AS DECIMAL)) ,0))
-- using '1e0 * column' implicitly converts mycolumn value to float
CONVERT(int, ROUND(AVG(1e0 * mycolumn) ,0))
-- the conversion to INT already rounds the value
CONVERT(INT, AVG(1e0 * mycolumn))
回答by Rambox
On SQL 2014,
在 SQL 2014 上，
select round(94,-1)
select round(95,-1)
