SQL:复杂的选择语句和返回的半径 [英] SQL: Complicated Select Statement and returned radius

查看：54
本文介绍了SQL:复杂的选择语句和返回的半径的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这个查询让我发疯，老实说我不知道​​如何完成它.

我需要获取特定半径内的所有用户和商店经度和纬度，并返回一些附加到这些结果 ID 的其他信息......半径部分，我已经弄清楚了:

( 3959 * acos( cos( radians('48.453541') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-123.491765') ) + sin( radians(')48.453541') ) * sin( radians( lat ) ) ) ) AS 距离

^以上语句中的弧度只是我要查询的随机地址.

但是抓取本质上是两个不同行的结果作为一个结果让我感到困惑.两个表都以这种方式构造:

id元键元值

所以我需要为 USER 和 SHOP 获取两个键的meta_value"......用户的meta_key"是bid_user_lat"和bid_user_lng"......以及用于SHOP 是bid_resource_lat"和bid_resource_lng"

理想的结果(只显示一个，但应该是多个，具体取决于半径):

id - 223first_name - 标记"姓氏 - 约翰逊"bid_user_lat - 45.0000bid_user_lng - -150.0000编号 - 688company_name - "乔的商店"bid_resource_lat - 45.0000bid_resource_lng - -150.0000

我真的希望我能正确解释这一点，因为这让我发疯！

<小时>

我更新的查询如下，但它仍然没有产生任何结果，它应该:

SET @lat = '48.453541';SET @lng = '-123.491765';设置@radius = '10000';选择wp_usermeta.user_id,bid_user_lat.meta_value，bid_user_lng.meta_value，(3959*acos(cos(弧度(@lat))*cos(弧度(bid_user_lat.meta_value))*cos(弧度(bid_user_lng.meta_value)-弧度(@lng))+sin(弧度(@lat))*罪(弧度(bid_user_lat.meta_value))))AS距离从wp_usermeta加入wp_usermeta bid_user_latON wp_usermeta.user_id = bid_user_lat.user_idAND wp_usermeta.meta_key = "bid_user_lat"加入wp_usermeta bid_user_lngON wp_usermeta.user_id = bid_user_lng.user_idAND wp_usermeta.meta_key = "bid_user_lng"有距离<@radius ORDER BY distance LIMIT 0 , 20;

解决方案

您可以通过使用自联接来获取此信息:

SELECT表.id，bid_user_lat.meta_value，bid_user_lng.meta_value从桌子加入表bid_user_latON table.id = bid_user_lat.idAND table.meta_key = "bid_user_lat"加入表bid_user_lngON table.id = bid_user_lng.idAND table.meta_key = "bid_user_lng"在哪里{ 距离条款 }

...距离条款是您确保它在适当距离内的地方.您可以在计算中使用 bid_user_lat.meta_value 作为 bid_user_lat 值，使用 bid_user_lng.meta_value 作为 bid_user_lng 值.

如果您想在与 Shop 结果相同的查询中返回 User 结果，那么您可以执行与上述相同的查询(当然使用 Shops 而不是 Users)，并用 将它们连接起来UNION 或 UNION ALL.

附注:

如果您对数据库有任何控制权，您可能需要重新访问设计.上面的解决方案无论如何都不理想......如果一个User总是有一个bid_user_lat和一个bid_user_lng字段，或者即使它只是User的一个公共属性，那么它真的应该在User表中有自己的列.

更正:

我 JOIN 错误.ON 子句应该使用 bid_user_lng.meta_key = "bid_user_lat" 而不是 wp_usermeta.meta_key = "bid_user_lat".您还需要添加一个 GROUP BY 子句，以便您没有重复的记录.将这些应用于上面的查询，您会得到:

SET @lat = '48.453541';SET @lng = '-123.491765';设置@radius = '10000';选择wp_usermeta.user_id,bid_user_lat.meta_value，bid_user_lng.meta_value，(3959*acos(cos(弧度(@lat))*cos(弧度(bid_user_lat.meta_value))*cos(弧度(bid_user_lng.meta_value)-弧度(@lng))+sin(弧度(@lat))*罪(弧度(bid_user_lat.meta_value))))AS距离从wp_usermeta加入wp_usermeta bid_user_latON wp_usermeta.user_id = bid_user_lat.user_idAND bid_user_lat.meta_key = "bid_user_lat"加入wp_usermeta bid_user_lngON wp_usermeta.user_id = bid_user_lng.user_idAND bid_user_lng.meta_key = "bid_user_lng"按 wp_usermeta.user_id 分组有距离<@radius ORDER BY distance LIMIT 0 , 20;

这里确认它有效:

mysql>创建表 wp_usermeta (user_id INTEGER UNSIGNED, meta_key VARCHAR(255), meta_value VARCHAR(255));查询正常，0 行受影响(0.25 秒)mysql>INSERT INTO wp_usermeta (user_id, meta_key, meta_value)->价值观->(1, "bid_user_lat", "45.000"), (1, "bid_user_lng", "-150.000"),->(2, "bid_user_lat", "20.000"), (2, "bid_user_lng", "20.000"),->(3, "bid_user_lat", "-300.000"), (3, "bid_user_lng", "70.000");查询正常，6 行受影响(0.16 秒)记录:6 重复:0 警告:0mysql>选择->wp_usermeta.user_id,->bid_user_lat.meta_value，->bid_user_lng.meta_value，->->(3959*acos(cos(弧度(@lat))*cos(弧度(bid_user_lat.meta_value))*cos(弧度(bid_user_lng.meta_value)-弧度(@lng))+sin(弧度(@lat))*罪(弧度(bid_user_lat.meta_value))))AS距离->->从->wp_usermeta->加入->wp_usermeta bid_user_lat->ON wp_usermeta.user_id = bid_user_lat.user_id->AND bid_user_lat.meta_key = "bid_user_lat"->加入->wp_usermeta bid_user_lng->ON wp_usermeta.user_id = bid_user_lng.user_id->AND bid_user_lng.meta_key = "bid_user_lng"->->按 wp_usermeta.user_id 分组->->有距离<@radius ORDER BY distance LIMIT 0 , 20;+---------+------------+------------+-------------------+|用户 ID |元值 |元值 |距离|+---------+------------+------------+-------------------+|1 |45.000 |-150.000 |1271.3329043047352 ||3 |-300.000 |70.000 |4905.4310014631055 ||2 |20.000 |20.000 |7198.549954690863 |+---------+------------+------------+-------------------+3 行(0.05 秒)

This query is driving me insane and I honestly just don't know how to accomplish it.

I need to grab all USERS and SHOPS longitude and latitude that are within a certain radius, and return back some other information attached to those resulting IDs... the radius part, I have figured out:

( 3959 * acos( cos( radians('48.453541') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-123.491765') ) + sin( radians('48.453541') ) * sin( radians( lat ) ) ) ) AS distance

^This radians in the statement above are just a random address that I want to query against.

But grabbing results that essentially are two different rows AS ONE result confuses me right out. Both tables are constructed in this manner:

id
meta_key
meta_value

So I need to grab the "meta_value" of two keys for both a USER and a SHOP ... the "meta_key" for the USER is "bid_user_lat" and "bid_user_lng" ... and the "meta_key" for the SHOP are "bid_resource_lat" and "bid_resource_lng"

Ideal results (just showing one of each, but should be multiple, depending on radius):

id - 223
first_name - "Mark"
last_name - "Johnson"
bid_user_lat - 45.0000
bid_user_lng - -150.0000

id - 688
company_name - "Joe's Shop"
bid_resource_lat - 45.0000
bid_resource_lng - -150.0000

I really hope I'm explaining this correctly, because this is just driving me nuts!

My updated query is as follow, yet it's still not producing any results and it should:

SET @lat =  '48.453541';
SET @lng =  '-123.491765';
SET @radius =  '10000';

SELECT
    wp_usermeta.user_id,
    bid_user_lat.meta_value,
    bid_user_lng.meta_value,

( 3959 * acos( cos( radians( @lat ) ) * cos( radians( bid_user_lat.meta_value ) ) * cos( radians( bid_user_lng.meta_value ) - radians( @lng ) ) + sin( radians( @lat ) ) * sin( radians( bid_user_lat.meta_value ) ) ) )  AS distance 

FROM
    wp_usermeta
JOIN 
    wp_usermeta bid_user_lat
    ON wp_usermeta.user_id = bid_user_lat.user_id
    AND wp_usermeta.meta_key = "bid_user_lat"
JOIN
    wp_usermeta bid_user_lng
    ON wp_usermeta.user_id = bid_user_lng.user_id
    AND wp_usermeta.meta_key = "bid_user_lng"

HAVING distance < @radius ORDER BY distance LIMIT 0 , 20;

解决方案

You can get this information by using self-joins:

SELECT
    table.id,
    bid_user_lat.meta_value,
    bid_user_lng.meta_value
FROM
    table
JOIN 
    table bid_user_lat
    ON table.id = bid_user_lat.id
    AND table.meta_key = "bid_user_lat"
JOIN
    table bid_user_lng
    ON table.id = bid_user_lng.id
    AND table.meta_key = "bid_user_lng"
WHERE
    { distance clause }

...the distance clause is where you ensure that it's within the proper distance. You can use bid_user_lat.meta_value as the bid_user_lat value, and bid_user_lng.meta_value as the bid_user_lng value in your calculation.

If you want to return the User results in the same query as the Shop results, then you can do the same query as the above one (using Shops instead of Users, of course), and connect them with a UNION or UNION ALL.

Side note:

If you have any control over the database, you may want to re-visit the design. The solution above is not ideal by any means... If a User always has a bid_user_lat and a bid_user_lng field, or even if it's just a common attribute of User, then it really should have its own column in the User table.

Correction:

I was JOINing incorrectly. The ON clause should use bid_user_lng.meta_key = "bid_user_lat" instead of wp_usermeta.meta_key = "bid_user_lat". You also need to add a GROUP BY clause so that you don't have duplicate records. Applying these to your query above, you get:

SET @lat =  '48.453541';
SET @lng =  '-123.491765';
SET @radius =  '10000';

SELECT
    wp_usermeta.user_id,
    bid_user_lat.meta_value,
    bid_user_lng.meta_value,

( 3959 * acos( cos( radians( @lat ) ) * cos( radians( bid_user_lat.meta_value ) ) * cos( radians( bid_user_lng.meta_value ) - radians( @lng ) ) + sin( radians( @lat ) ) * sin( radians( bid_user_lat.meta_value ) ) ) )  AS distance 

FROM
    wp_usermeta
JOIN 
    wp_usermeta bid_user_lat
    ON wp_usermeta.user_id = bid_user_lat.user_id
    AND bid_user_lat.meta_key = "bid_user_lat"
JOIN
    wp_usermeta bid_user_lng
    ON wp_usermeta.user_id = bid_user_lng.user_id
    AND bid_user_lng.meta_key = "bid_user_lng"

GROUP BY wp_usermeta.user_id

HAVING distance < @radius ORDER BY distance LIMIT 0 , 20;

Here's confirmation that it works:

mysql> CREATE TABLE wp_usermeta (user_id INTEGER UNSIGNED, meta_key VARCHAR(255), meta_value VARCHAR(255));
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
    -> VALUES
    -> (1, "bid_user_lat", "45.000"), (1, "bid_user_lng", "-150.000"),
    -> (2, "bid_user_lat", "20.000"), (2, "bid_user_lng", "20.000"),
    -> (3, "bid_user_lat", "-300.000"), (3, "bid_user_lng", "70.000");
Query OK, 6 rows affected (0.16 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->     wp_usermeta.user_id,
    ->     bid_user_lat.meta_value,
    ->     bid_user_lng.meta_value,
    -> 
    -> ( 3959 * acos( cos( radians( @lat ) ) * cos( radians( bid_user_lat.meta_value ) ) * cos( radians( bid_user_lng.meta_value ) - radians( @lng ) ) + sin( radians( @lat ) ) * sin( radians( bid_user_lat.meta_value ) ) ) )  AS distance 
    -> 
    -> FROM
    ->     wp_usermeta
    -> JOIN 
    ->     wp_usermeta bid_user_lat
    ->     ON wp_usermeta.user_id = bid_user_lat.user_id
    ->     AND bid_user_lat.meta_key = "bid_user_lat"
    -> JOIN
    ->     wp_usermeta bid_user_lng
    ->     ON wp_usermeta.user_id = bid_user_lng.user_id
    ->     AND bid_user_lng.meta_key = "bid_user_lng"
    -> 
    -> GROUP BY wp_usermeta.user_id
    -> 
    -> HAVING distance < @radius ORDER BY distance LIMIT 0 , 20;
+---------+------------+------------+--------------------+
| user_id | meta_value | meta_value | distance           |
+---------+------------+------------+--------------------+
|       1 | 45.000     | -150.000   | 1271.3329043047352 |
|       3 | -300.000   | 70.000     | 4905.4310014631055 |
|       2 | 20.000     | 20.000     |  7198.549954690863 |
+---------+------------+------------+--------------------+
3 rows in set (0.05 sec)

这篇关于SQL:复杂的选择语句和返回的半径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆