四舍五入到小数点后4位，但在MS SQL Server中显示的是小数点后4位+ 4个0

Question

我在HackerRank的17号天气观测站工作。查询STATION中最小的北纬(LAT_N)大于38.7780的西经(LONG_W)。你的答案四舍五入到小数点后四位。

表:车站字段:ID，城市，州，LAT_N, LONG_W其中LAT_N为北纬，LONG_W为西经。

我代码:

SELECT ROUND(LONG_W,4,0) AS low
FROM STATION
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

输出:70.13780000答案是错误的。我在网上查了这个问题，代码在其他答案中看起来是一样的。我用的是MS SQL Server。同样的代码在MySQL

上运行良好

Answer 1

ROUND函数将返回与输入相同的数据类型、精度和比例:

select round(1.10045001, 4); -- 1.10050000
select round(1.10055001, 4); -- 1.10060000

您需要CAST(... AS DECIMAL(..., 4))生成恰好4位的小数。该函数将在转换过程中使用与ROUND相同的算法舍入值:

select cast(1.10045001 as decimal(18, 4)); -- 1.1005
select cast(1.10055001 as decimal(18, 4)); -- 1.1006

Answer 2

我只是不会使用ROUND:

SELECT CONVERT(decimal(12,4), LONG_W) AS low
FROM STATION
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

也更有效率:

SELECT TOP (1) CONVERT(decimal(12,4), LONG_W) AS low
FROM STATION
WHERE LAT_N > 38.7780
ORDER BY LAT_N;

Answer 3

For SQL

SELECT CAST(LONG_W AS DECIMAL(10, 4))
FROM STATION
WHERE LAT_N = ( SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

Answer 4

MS SQL SERVER:

SELECT TOP 1 CAST(LONG_W AS DECIMAL(10,4)) FROM STATIONWHERE LAT_N>38.7780ORDER BY LAT_N ASC

Answer 5

SELECT FORMAT(round(min(LAT_N),4),'F4')
FROM STATION
WHERE LAT_N > 38.7780;

Your Output (stdout)
38.8526

Answer 6

实际上这个问题有点令人困惑，但在得到正确答案后，它仍然有一些意义。

LAT_N的条件令人困惑，这意味着LAT_N is smallest one and the same time LAT_N is greater than 38.7780

select round(LONG_W,4) from STATION where LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

stdout: 70.1378

Answer 7

应该可以。有几个lat_n值大于38.7780。但是，我们需要其中最小的一个，以及相应的long_w。因此，为了得到最小的lat_n，将结果按lat_n排序，并将极限设为1。这将选择最小的lat_n值。

SELECT ROUND(long_w, 4)
FROM station
WHERE lat_n > 38.7780
ORDER BY lat_n
LIMIT 1;

Answer 8

SELECT CAST(ROUND(LONG_W,4) AS DECIMAL(10, 4)) 
FROM STATION WHERE LAT_N = ( SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

this will work.

Answer 9

我的代码在MYSQL上运行成功

select round (LONG_W,4)
from STATION
where LAT_N>38.7780
order by LAT_N asc
limit 1;

Answer 10

这是MYSQL中这个问题的正确答案

select round (LAT_N,4)
from STATION
where LAT_N>38.7780
order by LAT_N asc
limit 1;

Answer 11

MS SQL的解决方案-避免在四舍五入的数字后面跟踪0的最好方法-如452.05 vs 452.050000是使用CAST AS DECIMAL这样的格式。对于MS SQL，我们需要使用'TOP'，但对于MySQL，它将是LIMIT

/*use of CAST function*/
select top 1 cast(long_w as decimal(10,4)) from station
where lat_n > 38.7780 order by lat_n asc

输出将是

70.1378