|
我有2张桌子。我的其中一张表users是:$ f8 p0 D' h `& ?, s% q3 g1 C
id name longitude latitutde $ V$ m9 z8 k9 o, z' g% h& X8 M" O
----- ----- ----------- -----------
% \7 Q, ~. P+ z* i3 K1 Mark -76.316528 40.036027; Y, T; E( O7 z; W
2 John -95.995102 41.25716 S# R _" v4 s5 `. \$ u9 x: M& A
3 Paul -82.337036 29.645095
8 ^7 ^ e+ X' {1 [9 V; L* p# N4 Dave -82.337036 29.645095: i/ x9 f+ k7 w, ~3 b1 Z2 T
5 Chris -76.316528 40.036027
7 \) ]2 `9 m0 L( c3 }我正在使用SQL检测附近的人:SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians(8 U; R/ _1 b4 f# N
latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37)# ]: P2 S, _( e* O5 S, @# x* i
) * sin(radians(latitude)) ) ) AS distance FROM用户HAVING distance
6 j5 }4 _- J9 u另一个matches是这样的:1 S4 W+ h ]6 S4 f/ e, F
id sender receiver status # Q# t9 P6 F$ a6 V+ @7 Z3 z
----- ----- ----------- -----------
7 F2 {; W3 ?; `+ \& x; X0 ?发送者是发送邀请者的人,接收者是接收者的人。
+ N$ y1 A+ A& \* [7 KSQL请求应在users人员附近搜索并检查matchesID。如果没有作为发送者和接收者的ID,则返回此人的用户ID以建立新的连接。
% H, j; F% Y0 o. w例如,保罗在寻找附近的人,而戴夫users则在他附近寻找。因此,仅当Dave ID不在matches表中或Paul == receiverAND
! u* Z9 A- v5 F& gstatus == 0时,才应将Dave ID返回给Paul 。% q% p; u: q- z2 z: b
如何编写此SQL查询以返回附近人员的20个ID?' L+ w: O! H1 |- K' e) T3 i
" t, K2 L% u. |' k9 \解决方案:, |; G1 `0 Z4 ^$ ^) j) l* K
u9 C. C- g) o% k! M& @4 M
% y, R9 ~+ Z( x; `# P
' Q8 A m2 n; d+ w/ h
我将距离公式更改为自mysql3 c" `1 b+ ] p o7 _, r5 s$ z
5.7起可用的格式。您没有_指定您的数据库或提供了一个小提琴示例来展示您的系统,因此我选择5.7作为mini9mum,它也可以在8.0中使用,就像您在小提琴中看到的那样。7 @2 ?4 P$ W; O u# C
内部查询需要两件事:搜索的用户ID和他的位置,因为从结果中排除了他,并且当然要计算距离。
) u+ D b0 e2 F! L$ ~; v$ Y查询的结果在where子句中检查-我希望我有正确的主意,因此您应该检查一下0 Y. O" `+ D% {) d. @3 z
, y B+ L" U; @& B9 bCREATE TABLE users
2 S6 V( l/ X- D0 O' ? (`id` varchar(5), `name` varchar(5), `longitude` varchar(11),
9 z! M1 m4 u) p8 W' z' K/ F- d' klatitude varchar(11))
8 X1 b: r$ ?1 k ;
7 l/ m$ V6 o4 I8 fINSERT INTO users/ ^" Z* q% N$ E' b0 \; O
(`id`, `name`, `longitude`, `latitude`); x. k' x9 U! \; r# {! P
VALUES( Q& Z/ V; r: u& r" G3 `
('1', 'Mark', '-76.316528', '40.036027'),+ Q1 z3 n# z1 }$ D& v6 J% S, W" P* q; E
('2', 'John', '-95.995102', '41.25716'),
3 s2 ^$ m* d" h0 w+ m( z% l( d# g ('3', 'Paul', '-82.337036', '29.645095'),0 @5 \' ]. e$ p4 R% e3 a7 o
('4', 'Dave', '-82.337036', '29.645095')," O/ n$ X2 n" B& }
('5', 'Chris', '-76.316528', '40.036027'),* r) O6 ^; W: }( p& O! e
('6', 'Manny', '-82.338036', '29.645095'),
8 Z& q8 \ h* e0 t7 R) z% S1 W2 w ('7', 'Fred', '-82.338036', '29.646095')0 |$ V6 E- ?5 n3 ]1 B2 O
;0 `" U+ K/ e" [5 I9 b
- Q3 s( H2 b/ @0 S* O1 G?
% P- F' e* B' O?
2 {4 h& q7 J ` G. L2 s. s
! P8 s* Q F [" cCREATE TABLE matches
/ ]- F0 P }% Q2 T2 v (`id` int, `sender` int, `receiver` int, `status` int)' `' o; Q+ I: B( A4 ]7 a8 Y
;1 F4 w8 I/ \+ V, A! m7 w3 Z4 J) c
INSERT INTO matches0 U* w5 }9 \8 T" Z9 X. _4 _
(`id`, `sender`, `receiver`, `status`)- Q' a' e4 ~9 w, I, u
VALUES7 I" l: J) g0 I3 }) g0 q
(1, 3, 4, 0),4 e" {7 R+ Q% t6 x, h
(2, 1, 5, 1),
7 Q- H) |# d8 j5 L: a& E" M& G2 M (3, 6, 3, 1)9 Q( F1 ]* B4 O. o
;3 } [, F) S7 M, G6 o
# T% s2 V* E4 S8 `0 ]" `+ A
?$ b& ~8 B+ X V5 S
?3 f b: e6 l' ~& L! d( R" h
SELECT
% K* Q& L+ q9 |& m- d F. w id# U8 W! |# S/ a' ^8 c
, ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) )% u# m l2 w! m
* cos( radians( longitude )4 V4 k) E4 t0 Y! B
- radians(-122) ) + sin( radians(37) )
3 v0 k j- g. z; I' Q * sin(radians(latitude)) ) ) AS distance$ c5 L) \$ p$ w2 K4 g
FROM
( U5 _! a' u6 Y7 |4 | users
5 Q G0 C+ U8 a# L- O. b) |( tHAVING distance POINT(longitude, latitude ))/1000 as distance2 O0 _& `# z" l% r6 W, v! _
FROM, K/ c7 O& I1 w3 E7 o. Q
users u9 b; L; L1 ~' x# v8 x" G* J
WHERE id 3. K4 a) B) k- V
HAVING distance ) s/ `: i$ K; F* u0 Q% u
id | distance0 ]/ _8 o4 o& d6 v3 S* _3 ^3 n$ c8 a
:-| ------------------:6 S) h5 c1 X* U" q$ T0 }
6 | 0.096639954458951396 t7 n' I! `' V& ^/ Q( ]
7 | 0.14732089261518266 |
|