我有两个sql查询,当独立运行时,会产生正确的结果。
查询1
SELECT id,
(6371 * acos( cos( radians(9.977364864079215) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.58620953448485) ) + sin( radians(9.977364864079215) ) * sin( radians( latitude ) ) ) )
AS distance
FROM geodata HAVING distance < 20
ORDER BY distance
LIMIT 0 , 20; 查询2
SELECT DISTINCT e.* FROM schools e
WHERE (
(e.type = 'preprimary')
)
AND(
e.title LIKE '%government%'
)
LIMIT 0, 10我想将第一个查询和第二个查询合并,这样它应该返回所有“学前”类型的学校,比如位于20公里半径范围内的“政府”,结果需要按距离排序。
如何合并这两个查询?我试着加入学校桌子上的地理数据表。但我不知道剩下的。抱歉,如果这是个愚蠢的问题。我对SQL世界非常陌生。
发布于 2014-03-30 19:49:45
试试这个:
SELECT *
From (
SELECT DISTINCT e.* ,
(6371 * acos( cos( radians(9.977364864079215) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.58620953448485) ) + sin( radians(9.977364864079215) ) * sin( radians( latitude ) ) )
) as distance
FROM schools e
LEFT JOIN geodata g ON e.id=g.id
WHERE (e.type = 'preprimary')
AND ( e.title LIKE '%government%' )
) as s
Where s.distance < 20
Order by s.distance发布于 2014-03-30 19:20:45
SELECT DISTINCT school.* FROM
( SELECT geodata.id,
(6371 * acos( cos( radians(9.977364864079215) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.58620953448485) ) + sin( radians(9.977364864079215) ) * sin( radians( latitude ) ) ) )
AS distance ,school.*
FROM geodata LEFT JOIN school on geodata.id=school.id
WHERE
(school.type = 'preprimary')
AND(
school.title LIKE '%government%'
)
AND school.id IS NOT NULL
HAVING distance < 20 )x
ORDER BY x.distance
LIMIT 0 , 10; https://stackoverflow.com/questions/22748348
复制相似问题