如何用acos-cos-radians公式选择不同的一列,但返回所有列。

我想获取一个名为 “supplier_products “的表,该表的列数如下。

ID Item_id supplier_id variant_id  price    lat        lng   serving_radius(in km)

1     1      2            12       22.00  26.11360000 85.39430000    1
2     1      3            12       44.00  26.11360000 85.39430000    4
3     1      2            13       25.00  26.11360000 85.39430000    4
4     1      3            13       23.00  26.11360000 85.39430000    4

现在搜索经纬度附近的供应商产品时,可以说是($lat = 26.1136;$long = 85.3643;) ……我使用了这个查询

SELECT *, (6371 * acos(cos(radians('$lat')) * cos(radians(lat)) * cos( radians(lng) - radians('$long')) + sin(radians('$lat')) * sin(radians(lat)))) AS distance FROM supplier_products HAVING distance <= serving_radius ORDER BY distance")

上面的查询返回所有服务于输入的记录。$lat & $long.

但现在,我想返回所有的列,只有那些具有独特的variant_id的行,这是为输入的 $lat & $long

我试着用一个 GROUP BY

SELECT *, (6371 * acos(cos(radians('$lat')) * cos(radians(lat)) * cos( radians(lng) - radians('$long')) + sin(radians('$lat')) * sin(radians(lat)))) AS distance FROM supplier_products GROUP BY variant_id HAVING distance <= serving_radius")

但它消除了一些想要的行,因为 GROUP BY 正在进行前 HAVING 子句。所以它消除了一些在服务半径内的所需行。

我使用的是 PHP & MYSQL

EDIT–我想把这个作为我的输出

ID Item_id supplier_id variant_id  price    lat        lng   serving_radius(in km)

2     1      3            12       44.00  26.11360000 85.39430000    4
3     1      2            13       25.00  26.11360000 85.39430000    4

作为行与 ID-1 不服务于输入 $lat/$long

但我的尝试给出了以下结果–

ID Item_id supplier_id variant_id  price    lat        lng   serving_radius(in km)

3     1      2            13       25.00  26.11360000 85.39430000    4

因为 GROUP BY 消除了 2nd row

解决方案:

如果你想得到可预测的结果,你的要求还是不完整。你要求从几条记录中只选择一条。为什么你想要的结果显示的是id=3而不是id=4?我想你是随机选择了其中的一条。

这个查询 (https:/www.db-fiddle.comfUSMrhc8gLcRD2rAmuzYzH0)为你做到了。

SET @lat := 26.120888;
SET @long := 85.364832;
SELECT ANY_VALUE(supplier_id), variant_id, ANY_VALUE(price)  
FROM  ( SELECT ID,
               (6371 * acos(cos(radians(@lat)) * cos(radians(lat)) *
                cos( radians(lng) - radians(@long)) + sin(radians(@lat)) * 
                sin(radians(lat)))) AS distance
          FROM supplier_products 
     ) t WHERE distance < serving_radius
GROUP BY variant_id

它有一个子查询来显示你的表的一个变体,显示与所提供的@lat和@long值的距离。 它显示ID和距离。这就是它。(https:/www.db-fiddle.comfguagWYVXXaf7cPkbojj9KD2)

        SELECT *, 
               (6371 * acos(cos(radians(@lat)) * cos(radians(lat)) *
                cos( radians(lng) - radians(@long)) + sin(radians(@lat)) * 
                sin(radians(lat)))) AS distance
          FROM supplier_products 

然后,它在一个带有GROUP BY的外部查询中使用该子查询,见上文。

但是,因为你只想要每条变量的一条记录,而且你还没有告诉我们如何在有多条记录的情况下选择那条记录,所以查询使用了 ANY_VALUE()函数 为你的结果中的每一列选择一个可用的值。

比8.0旧的MySQL版本不需要ANY_VALUE()函数,因为MySQL对GROUP BY的处理是出了名的不标准。请阅读以下内容。

请注意,如果你让MySQL使用这个ANY_VALUE()函数。 如果你让MySQL使用这些ANY_VALUE()的东西,不管是隐式的还是显式的,你都会发现 让你的测试人员和用户疯狂。他们有时会在今天得到与上周不同的结果,他们会想知道自己做错了什么。请不要这样做。

还有一件事:你的距离公式在很小的距离上抛出异常是出了名的。通过像这样修改它来使用LEAST(),确保它永远不会尝试使用ACOS()来处理大于1的值。(https:/www.db-fiddle.comfUSMrhc8gLcRD2rAmuzYzH1)

    (6371 * acos(LEAST(1.0,cos(radians(@lat)) * cos(radians(lat)) *
    cos( radians(lng) - radians(@long)) + sin(radians(@lat)) *
    sin(radians(lat)))))

给TA打赏
共{{data.count}}人
人已打赏
解决方案

神经网络回归预测所有测试样本的相同值

2022-5-13 15:00:18

解决方案

获取Word字符串的函数

2022-5-13 15:00:20

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索