如何选择不同的一列，但使用acos-cos-radians公式返回所有列 [英] How to select distinct one column but return all columns with acos-cos-radians formula

问题描述

我想获取一个名为Supplier_products的表，该表具有以下列

I want to fetch a table named supplier_products which has following columns

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;）。我正在使用此查询

Now for searching supplier products near a latitude and longitude say($lat = 26.1136;$long = 85.3643;) . i was using this query

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

上面的查询返回为输入<$ c $提供服务的所有行c> $ lat& $ long 。

The above query returns all rows which serves the input $lat & $long.

但是现在我只想返回那些具有唯一的variant_id的行的所有列作为输入 $ lat& $ long

But now i want to return all columns of only those rows having distinct variant_id which serves the input $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 子句之前重构。因此，它消除了服务半径内的某些必需行。

But it eliminates some desired rows as GROUP BY is being performed before the HAVING clause. So it eliminates some required rows which are in the serving radius.

我正在使用 PHP & MYSQL

I am using PHP & MYSQL

编辑-我希望将此作为输出

EDIT- I want this as my output

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

As row with ID-1 does not serves the input $lat/$long

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

But my attempt gave the following result -

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 消除了第二行

推荐答案

如果您想要可预测的结果，您的要求仍然不完整。您要求仅从几行中选择一行。为什么您想要的结果显示id = 3而不是id = 4？我猜想您是随机选择的。

Your requirement is still incomplete if you want predictable results. You call for choosing just one row from among several. Why did your desired result show id=3 and not id=4? I guess you chose one of the two at random.

此查询（ https://www.db-fiddle.com/f/USMrhc8gLcRD2rAmuzYzH/0 ）为您做到这一点。

This query (https://www.db-fiddle.com/f/USMrhc8gLcRD2rAmuzYzH/0) does that for you.

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）

It has a subquery to show a variant of your table that shows distances from the @lat and @long values supplied. It shows ID and distance. This is it. (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 

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

It then uses that subquery in an outer query with a GROUP BY, see above.

但是，因为每个变体只需要一行，并且您没有告诉我们如果有多行，如何选择该行，因此查询使用 ANY_VALUE（）函数中选择一个

But, because you want just one row per variant and you haven't told us how to pick that row if there are multiple rows, the query uses the ANY_VALUE() function to choose one of the available values for each column in your result.

版本低于8.0的MySQL版本不需要ANY_VALUE（ ）的功能，因为MySQL对GROUP BY的臭名昭著的非标准处理：请仔细阅读。

Versions of MySQL older than 8.0 don't need the ANY_VALUE() functions, because of MySQL's notorious nonstandard handling of GROUP BY: please read this.

当心：：如果让MySQL使用此ANY_VALUE（）内容（无论是隐式的还是显式的），您将使您的测试人员和用户发疯。他们今天有时会得到与上周不同的结果，他们会想知道自己做错了什么。

Beware: if you let MySQL use this ANY_VALUE() stuff, whether implicit or explicit, you will drive your testers and your users crazy. They'll sometimes get different results today than they got last week, and they'll wonder what they did wrong. Please don't do it.

还有另一件事：您的距离公式因在很小的距离上抛出异常而臭名昭著。通过像这样修改它以使用LEAST（），确保它永远不会尝试使用大于1的值的ACOS（）。 （ https://www.db-fiddle.com/f/USMrhc8gLcRD2rAmuzYzH/1）

And one other thing: your distance formula is notorious for throwing exceptions on very small distances. Make sure it never tries to use ACOS() with a value greater than 1 by modifying it like this to use 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)))))

这篇关于如何选择不同的一列，但使用acos-cos-radians公式返回所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！