如何在mysql MIN()聚合函数中比较科学计数法和小数？

发布于 2024-12-19 08:10:41 字数 542 浏览 1 评论 0原文

我如何将 mysql MIN() 应用于混合十进制值和科学记数法值。我需要在 mysql 查询中使用 MIN() 使用纬度经度方程获得最小距离。如果距离是小数，则有效。但如果某个距离太小，如“5.89872212195226e-05”，则它无法正常工作。

问题是 mysql 查询中的 MIN(4,5.89872212195226e-05) 将始终返回“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

有解决方案吗？谢谢

原文

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"

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

Here is the part of query

 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

Any solution? Thank You

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

却一份温柔 2024-12-26 08:10:42

转换为十进制应该有帮助：-

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)))

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 |
+--------------------------------------------------+

Example of comparison :-

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

Example usage :-