如何在SQL Server中划分两个整数后得到最接近的值 [英] How do I get value closest to after dividing two integer in SQL server

问题描述

如果值为75.5或更高我想要返回76且小于75.5我想返回75.



这是我的查询的样子

if value is 75.5 or more i want to return 76 and less than 75.5 I want to return 75.

This is what my query looks like

SELECT DISTRICT, 
COUNT(DISTRICT) RegionDistrict,
CEILING(SUM(CONVERT(int,[turnoverSM (%)]))/COUNT(DISTRICT)) as SM,
CEILING(SUM(CONVERT(int,[turnoverASM (%)]))/COUNT(DISTRICT)) as ASM
FROM HRStaffTable WHERE REGION = 40 AND Created_Date = CONVERT(DATE, GetDate()) GROUP BY DISTRICT

我尝试过：



SELECT CEILING（1060/14）作为结果

What I have tried:

SELECT CEILING(1060/14) as result

推荐答案

当你分割两个整数时，结果总是被截断。如果你想改为对结果进行舍入，那么其中一个值必须是一个浮点类型。

When you divide two integers, the result will always be truncated. If you want to round the result instead, then one of the values needs to be a floating-point type.

ROUND(CONVERT(real, SUM([turnoverSM (%)])) / COUNT(DISTRICT), 0)

ROUND（Transact-SQL）| Microsoft Docs [ ^ ]



另外，如果你还没有意识到这一点，那对于桌子来说这是一个非常坏主意或列名称包含空格或其他特殊字符。如果可能，您应该重新设计数据库以重命名列，以便它们只使用字母数字字符。

ROUND (Transact-SQL) | Microsoft Docs[^]

Also, in case you haven't realised it yet, it's an extremely bad idea for table or column names to contain spaces or other special characters. If possible, you should redesign your database to rename the columns so that they only use alphanumeric characters.

这篇关于如何在SQL Server中划分两个整数后得到最接近的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！