消除子查询的平均值

小编典典

消除子查询的平均值

sql

寻求

该查询选择所有以“ Vancouver”开头的点，并且距所有以“
Vancouver”开头的位置的中心相距5分钟以内。例如，温哥华南弗雷泽（Vancouver South Fraser），温哥华锦绣（Vancouver
Fairview）和温哥华巴兰特里广场（Vancouver Ballantree Place
W）在其平均纬度和经度的5分钟内具有纬度和经度。纬度和经度存储为（4915，12311）整数对（表示49.15’N和123.11’W）。

SQL代码

以下SQL令人讨厌的技巧：

SELECT
  NAME
FROM
 STATION
WHERE
      DISTRICT_ID = '110'
  AND NAME LIKE 'Vancouver%'
  AND LATITUDE BETWEEN
    (SELECT round((min(LATITUDE) + max(LATITUDE)) / 2)-5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%')
    and
    (SELECT round((min(LATITUDE) + max(LATITUDE)) / 2)+5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%')
  AND LONGITUDE BETWEEN
    (SELECT round((min(LONGITUDE) + max(LONGITUDE)) / 2)-5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%')
    and
    (SELECT round((min(LONGITUDE) + max(LONGITUDE)) / 2)+5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%')
ORDER BY
  LATITUDE

问题

如何在不使用视图的情况下简化此查询以删除冗余？

限制

该数据库是MySQL，但ANSI SQL总是不错的。

谢谢！

阅读 202

2021-04-14

共1个答案

小编典典

select 
  name
from 
  (select 
    round((min(LATITUDE) + max(LATITUDE)) / 2) as LATITUDE,
    round((min(LONGITUDE) + max(LONGITUDE)) / 2) as LONGITUDE
   from STATION 
   where DISTRICT_ID = '110' 
     AND NAME LIKE 'Vancouver%') AS center
  inner join STATION s
where
  s.DISTRICT_ID = '110' 
  and s.NAME like 'Vancouver%'
  and s.LATITUDE between center.LATITUDE - 5 and center.LATITUDE + 5
  and s.LONGITUDE between center.LONGITUDE - 5 and center.LONGITUDE + 5

2021-04-14