如何在mysql MIN（）聚合函数中比较科学计数法和十进制数？ [英] How to compare scientific notation and decimal numbers in mysql MIN() aggregate function?

问题描述

如何在混合的十进制值和科学计数法值上应用mysql MIN（）。我需要在带有MIN（）的mysql查询中使用纬度经度方程获取最小距离。如果距离是十进制，则它正在工作。但是它不能正常工作，如果某些距离太短，例如 5.89872212195226e-05

How can i apply mysql MIN() over mixed decimal value and scientific notation values. I need to get minimum distance using latitude longitude equation in a mysql query with MIN(). It is working if the distance are decimal. But it is not working properly, if some distance are too small like "5.89872212195226e-05"

问题是mysql中的MIN（4,5.89872212195226e-05）查询将始终返回 4。

The problem is MIN(4,5.89872212195226e-05) in a mysql query will always return '4'.

这是查询的一部分

 MIN (
         (acos(sin((".$searchLat."*pi()/180)) * 
         sin((b_loc.locLatitude*pi()/180))+cos((".$searchLat."*pi()/180)) * 
         cos((b_loc.locLatitude*pi()/180))
         * cos(((".$searchlng."- b_loc.locLongitude)*pi()/180))))*180/pi())*60*1.1515
        ) as mindistance

任何解决方案？谢谢

推荐答案

从小数点转换为小数点应该有帮助：-

Cast to decimal should help :-

mysql> select cast( "5.89872212195226e-05"  as decimal(65,30));
+--------------------------------------------------+
| cast( "5.89872212195226e-05"  as decimal(65,30)) |
+--------------------------------------------------+
|                 0.000058987221219522600000000000 |
+--------------------------------------------------+

比较示例：-

mysql> select least( 4, cast("5.89872212195226e-05" as decimal(65,30)) );
+------------------------------------------------------------+
| least( 4, cast("5.89872212195226e-05" as decimal(65,30)) ) |
+------------------------------------------------------------+
|                           0.000058987221219522600000000000 |
+------------------------------------------------------------+

用法示例：-

MIN(cast( ...  as decimal(65,30)))

这篇关于如何在mysql MIN（）聚合函数中比较科学计数法和十进制数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！