MySQL 请求在 2 个表中进行组合和搜索 [英] MySQL request for combining and searching in 2 tables

问题描述

我有两张桌子.我的 users 表之一是:

id name 经度纬度----- ----- ----------- -----------1 马克 -76.316528 40.0360272 约翰 -95.995102 41.257163 保罗 -82.337036 29.6450954 戴夫 -82.337036 29.6450955 克里斯 -76.316528 40.036027

我使用 SQL 来检测附近的人:SELECT id, (3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(latitude)) ) ) AS距离来自usersHAVING distance <50 ORDER BY distance DESC LIMIT 20

其他matches是这样的:

id 发送者接收者状态----- ----- ----------- -----------

其中sender 是发送邀请的人，receiver 是接收者.

SQL 请求应该在 users 中搜索附近的人，并在 matches 中检查他们的 id.如果 id 不存在作为发件人 &接收者返回此人的用户 ID 以建立新连接.

例如，Paul 正在搜索附近的人，而我们从 users 看到的 Dave 就在他附近.因此，如果 Dave ID 不在 matches 表中或 Paul == receiver AND status == 0.

中，则应仅将 Dave ID 返回给 Paul

这个 SQL Query 应该如何编写以返回附近人的 20 个 ID?

解决方案

我将距离公式更改为自 mysql 5.7 以来有效的内容.您没有_指定您的数据库或提供一个小提琴示例来展示您的系统，所以我选择了 5.7 作为 mini9mum 并且它也可以在 8.0 中工作，正如您在小提琴中看到的那样.

内部查询需要两件事:搜索的用户 id 和他的位置，因为他被排除在结果之外，当然还要计算距离.

查询的结果在 where 子句中检查 - 我希望我有正确的想法，所以你应该检查

<块引用>

创建表用户(`id` varchar(5), `name` varchar(5), `longitude` varchar(11), `latitude` varchar(11));插入用户(`id`, `name`, `longitude`, `latitude`)价值观('1', '标记', '-76.316528', '40.036027'),('2', '约翰', '-95.995102', '41.25716'),('3', '保罗', '-82.337036', '29.645095'),('4', '戴夫', '-82.337036', '29.645095'),('5', '克里斯', '-76.316528', '40.036027'),('6', '曼尼', '-82.338036', '29.645095'),('7', '弗雷德', '-82.338036', '29.646095');

<前>✓✓

<块引用>

CREATE TABLE 匹配(`id` int, `sender` int, `receiver` int, `status` int);INSERT INTO 匹配(`id`, `sender`, `receiver`, `status`)价值观(1, 3, 4, 0),(2, 1, 5, 1),(3, 6, 3, 1);

<预>✓✓

<块引用>

SELECTID, ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) )* cos(弧度(经度)- 弧度(-122))+罪(弧度(37))* sin(radians(latitude)) ) ) AS距离从用户有距离<50按距离排序降速限制 20

<前>身份证 |距离:- |-------:

<块引用>

SELECT援助,a.距离从(选择ID，st_distance_sphere(POINT(-82.337036, 29.645095), POINT(`longitude`, `latitude`))/1000 作为距离从用户你WHERE id <>3有距离<50按距离排序DESC 限制 20) a在哪里a.id in (SELECT `sender` FROM 匹配 WHERE status = 1)或 a.id NOT IN(SELECT `sender` FROM 匹配 UNION ALL SELECT `receiver` FROM 匹配)ORDER BY A.distance ASC

<前>身份证 |距离:- |------------------:6 |0.096639954458951397 |0.14732089261518266

db<>fiddle 这里

I have 2 tables. One of my tables with users is:

id         name     longitude       latitutde  
-----      -----    -----------    -----------
1          Mark     -76.316528     40.036027
2          John     -95.995102     41.25716
3          Paul     -82.337036     29.645095
4          Dave     -82.337036     29.645095
5          Chris    -76.316528     40.036027

I'm using SQL to detect nearby people: SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(latitude)) ) ) AS distance FROMusersHAVING distance < 50 ORDER BY distance DESC LIMIT 20

The other matches is like this:

id         sender    receiver        status  
-----      -----    -----------    -----------

Where sender is the person who sent the invitation receiver is the person who receives is.

The SQL request should search in users for near by people and check in the matches for their ids. If the ids are not there as sender & receiver return the userID of the person to make a new connection.

For example Paul is searching for nearby people and Dave as we see from users is nearby him. So Dave ID should be returned to Paul ONLY , if their ids are not in matches table OR Paul == receiver AND status == 0.

How this SQL Query should be written to return 20 Ids of the nearby people ?

解决方案

I change the distance formula to something that works since mysql 5.7. You didn't_ specify your database or presented a fiddle example to show your system, so i selected 5.7 as mini9mum and it works also in 8.0 as you see in the fiddle.

the inner query needs two things the user id who searches and his position, because he is excluded from the results and of course to calculate the distance.

The result from the query is checked in the where clause-I hope i got the right idea, so you should check that

CREATE TABLE users
    (`id` varchar(5), `name` varchar(5), `longitude` varchar(11), `latitude` varchar(11))
;

INSERT INTO users
    (`id`, `name`, `longitude`, `latitude`)
VALUES
    ('1', 'Mark', '-76.316528', '40.036027'),
    ('2', 'John', '-95.995102', '41.25716'),
    ('3', 'Paul', '-82.337036', '29.645095'),
    ('4', 'Dave', '-82.337036', '29.645095'),
    ('5', 'Chris', '-76.316528', '40.036027'),
    ('6', 'Manny', '-82.338036', '29.645095'),
    ('7', 'Fred', '-82.338036', '29.646095')
;

✓

✓

CREATE TABLE matches
    (`id` int, `sender` int, `receiver` int, `status` int)
;

INSERT INTO matches
    (`id`, `sender`, `receiver`, `status`)
VALUES
    (1, 3, 4, 0),
    (2, 1, 5, 1),
    (3, 6, 3, 1)
;

✓

✓

SELECT 
  id
  , ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) 
     * cos( radians( longitude ) 
     - radians(-122) ) + sin( radians(37) ) 
     * sin(radians(latitude)) ) ) AS distance 
FROM
    users
HAVING distance < 50 
ORDER BY distance 
DESC LIMIT 20

id | distance
:- | -------:

SELECT
a.id
,a.distance
FROM
  (Select  
       id,
       st_distance_sphere(POINT(-82.337036, 29.645095 ), POINT(`longitude`, `latitude` ))/1000  as distance
    FROM
        users u  
    WHERE id <> 3 
    HAVING distance < 50 
    ORDER BY distance 
    DESC LIMIT 20) a
WHERE
    a.id in (SELECT `sender` FROM matches WHERE status = 1)
    OR a.id NOT IN ( SELECT `sender` FROM matches UNION ALL SELECT `receiver` FROM matches )
ORDEr BY a.distance ASC

id |            distance
:- | ------------------:
6  | 0.09663995445895139
7  | 0.14732089261518266

db<>fiddle here

这篇关于MySQL 请求在 2 个表中进行组合和搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！