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

Question

我想获取一个名为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

编辑-我想要这个作为我的输出

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

Answer 1

如果您想要可预测的结果，您的需求仍然不完整。您需要从多个行中选择一个行。为什么您想要的结果显示id=3而不是id=4？我想你是随机选择了其中之一。

这个查询(https://www.db-fiddle.com/f/USMrhc8gLcRD2rAmuzYzH/0)为您完成了这个任务。

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.com/f/guagWYVXXaf7cPkbojj9KD/2)

        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 

然后，它在带有组的外部查询中使用该子查询，参见上文。

但是，由于每个变量只需要一行，而且还没有告诉我们如何在有多个行的情况下选择该行，所以查询使用值()函数为结果中的每个列选择一个可用的值。

大于8.0的MySQL版本不需要ANY_VALUE()函数，因为MySQL对GROUP：请读一下这个。的非标准处理是臭名昭著的

警告:如果您让MySQL使用这个ANY_VALUE()东西，不管是隐式还是显式的，都会把测试人员和用户逼疯。他们有时会得到与上周不同的结果，他们会怀疑自己做错了什么。请不要这样做。

还有一件事:你的距离公式因在很小的距离上抛出异常而臭名昭著。要确保它不会尝试使用值大于1的ACOS()，方法是像这样将其修改为使用LEAST()。(https://www.db-fiddle.com/f/USMrhc8gLcRD2rAmuzYzH/1)

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