MySQL 查询无法正确显示结果 [英] MySQL query doesn't show results properly
问题描述
我有两张桌子.我的 users 表之一是:
id name 经度纬度----- ----- ----------- -----------1 马克 -82.347036 29.65450952 约翰 -82.357036 29.6650953 保罗 -82.367036 29.6450954 戴夫 -82.337036 29.6750955 克里斯 -82.437036 29.5750956 曼尼 -82.538036 29.7450957 弗雷德 -82.638036 29.346095我正在使用 SQL 来检测附近的人:
SELECT id,(3959 * acos(cos(弧度(37))* cos(弧度(纬度))*cos(弧度(经度)-弧度(-122))+罪(弧度(37))*罪(弧度(纬度)))) AS 距离从`用户`有距离<50 ORDER BY 距离 DESC LIMIT 20`其他matches是这样的:
id 发送者接收者状态----- ----- ----------- -----------1 3 4 02 1 5 13 6 3 14 2 6 05 2 1 0其中sender 是发送邀请的人，receiver 是接收者.
我的查询:
SELECT援助,a.距离从(选择ID，st_distance_sphere(POINT(-82.337036, 29.645095),POINT(`longitude`, `latitude`))/1000 作为距离从用户你WHERE id <>1有距离<5000按距离排序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我的小提琴:https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=95972531a390a013796ed4cf7ad99884
在匹配表中，Id 1 仅与 Id 5 匹配(其中状态为 1).
部分:
a.id in ( SELECT `sender` FROM 匹配 WHERE status = 1)或 a.id NOT IN ( SELECT `sender` FROM 匹配UNION ALL SELECT `receiver` FROM 匹配)在某处是错误的，因为它必须显示 id 2,3,4,6,7 不仅是 6 &7 .查看小提琴中的最后一个查询
查询必须符合逻辑:
如果我是 ID 为 1 的 sender，请不要返回我 receiver(a.id).
如果我是 id 1 的 receiver 返回我 sender(a.id) where status =0.
如果我不是 sender 或 receiver 返回我 a.id
当我使用你的逻辑时
你想要:
SELECTa.id, a.distance从(选择ID，ST_DISTANCE_SPHERE(POINT(- 82.337036, 29.645095), POINT(`longitude`, `latitude`))/1000 AS 距离从用户你在哪里id <>1有距离<5000按距离排序 DESC限制 20) a在哪里a.id NOT IN (SELECT `sender` FROM (SELECT `sender` FROM 匹配 UNION SELECT `receiver` FROM 匹配) t1WHERE `sender` IN (SELECT `sender` FROM 匹配 WHERE `receiver` = 1 AND status = 1UNION SELECT `receiver` FROM 匹配 WHERE `sender` = 1))按距离 ASC 排序<前>身份证 |距离:- |-----------------:3 |2.89919862564678652 |2.9462981804211044 |3.3358404689536967 |44.20441197234838
db<>fiddle 这里
I have 2 tables. One of my tables with users is:
id name longitude latitutde
----- ----- ----------- -----------
1 Mark -82.347036 29.6545095
2 John -82.357036 29.665095
3 Paul -82.367036 29.645095
4 Dave -82.337036 29.675095
5 Chris -82.437036 29.575095
6 Manny -82.538036 29.745095
7 Fred -82.638036 29.346095
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
FROM `users` HAVING distance < 50 ORDER BY distance DESC LIMIT 20`
The other matches is like this:
id sender receiver status
----- ----- ----------- -----------
1 3 4 0
2 1 5 1
3 6 3 1
4 2 6 0
5 2 1 0
Where sender is the person who sent the invitation receiver is the person who receives is.
My Query:
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 <> 1
HAVING distance < 5000
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
My fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=95972531a390a013796ed4cf7ad99884
In matches table Id 1 have match only with Id 5 (where status is 1).
The part with:
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 )
is wrong somewhere, because it has to show id 2,3,4,6,7 not only 6 & 7 . Check out last query in fiddle
The query must be with logic:
If I am the sender with id 1 don't return me receiver(a.id).
If I'm receiver with id 1 return me sender(a.id) where status = 0.
If I'm not sender or receiver return me a.id
When I use your logic
You want:
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 <> 1
HAVING distance < 5000
ORDER BY distance DESC
LIMIT 20) a
WHERE
a.id NOT IN (SELECT `sender` FROM (SELECT `sender` FROM matches UNION SELECT `receiver` FROM matches) t1
WHERE `sender` IN (SELECT `sender` FROM matches WHERE `receiver` = 1 AND status = 1
UNION SELECT `receiver` FROM matches WHERE `sender` = 1
))
ORDER BY a.distance ASC
id | distance :- | -----------------: 3 | 2.8991986256467865 2 | 2.946298180421104 4 | 3.335840468953696 7 | 44.20441197234838
db<>fiddle here
这篇关于MySQL 查询无法正确显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
