消除子查询的平均值 [英] Eliminate subquery for average numeric value

问题描述

任务

查询将选择以"Vancouver"开头的所有点，并且距离所有以"Vancouver"开头的位置的中心相距5分钟以内.例如，温哥华南弗雷泽(Vancouver South Fraser)，温哥华锦绣(Vancouver Fairview)和温哥华巴兰特里广场(Vancouver Ballantree Place W)在其平均纬度和经度的5分钟内具有纬度和经度.经度和纬度存储为(4915，12311)个整数对(即49.15'N和123.11'W).

The query selects all the points beginning with "Vancouver" and are within a 5 minute area from the center of all locations beginning with "Vancouver". For example, Vancouver South Fraser, Vancouver Fairview, and Vancouver Ballantree Place W have latitudes and longitudes within 5 minutes of their average latitude and longitude. The latitudes and longitudes are stored as (4915, 12311) integer pairs (meaning 49.15'N and 123.11'W).

SQL代码

以下SQL令人讨厌的方法可以达到目的:

The following SQL abomination does the trick:

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

问题

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

How can this query be simplified to remove the redundancy, without using a view?

限制

数据库是MySQL，但ANSI SQL总是很好.

The database is MySQL, but ANSI SQL is always nice.

谢谢！

推荐答案

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

这篇关于消除子查询的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！